探讨:如何查看和获取SQL Server实例名

一、查看實例名時可用

1、服务—sql server(实例名)，默认实例为(mssqlserver)

或在连接企业管理时-查看本地实例

2、通過注冊表
hkey_local_machine/software/microsoft/microsoft sql server/installedinstance

3、用命令
sqlcmd/osql
sqlcmd -l
sqlcmd -lc
osql -l

获取可用實例,以下舉一個例子，根據自己情況改

declare @table table ( instancename  sysname null)

insert @table exec sys.xp_cmdshell 'sqlcmd -lc'

--left(@@servername,charindex('/',@@servername+'/')-1) 替代為本機名就行了 , 根據實例命名規則判斷

select * from @table where instancename like   left( @@servername , charindex ( '/' , @@servername + '/' )- 1)+ '%'

二、

--1.
select serverproperty('instancename')

--2
sp_helpserver

--3
select @@servername

--4
select * from sys.sysservers

--5
select * from sys.servers

三、

execute xp_regread @rootkey='hkey_local_machine',
@key='software/microsoft/microsoft sql server/instance names/sql',
@value_name='mssqlserver'

四、

select case
when serverproperty ('instancename') is null then @@servername
else serverproperty ('instancename')
end

五、在本地或网络得到所有实例名

1、you can do with registry reading , like my code

using system;
using microsoft.win32;

namespace smotest
{
    class program
    {
      static void main()
      {
        registrykey rk = registry.localmachine.opensubkey(@"software/microsoft/microsoft sql server");
        string[] instances = (string[])rk.getvalue("installedinstances");
        if (instances.length > 0)
        {
           foreach (string element in instances)
           {
              if (element == "mssqlserver")
                 console.writeline(system.environment.machinename);
              else
                 console.writeline(system.environment.machinename + @"/" + element);
           }
        }
      }
    }
}

2、you can use sqldmo.dll to retrieve the list of sql server instances.  the sqldmo.dll can be found from the "c:/program files/microsoft sql server/80/tools/bin" folder. refer this assembly in your project and the following snippet would return a list object containing the sql server instances.

public static list getsqlserverinstances()
{
namelist sqlnamelist = null;
application app = null;

var sqlservers = new list();
try
{
app = new applicationclass();
sqlnamelist = app.listavailablesqlservers();
foreach (string sqlserver in sqlnamelist)
sqlservers.add(sqlserver);
}
catch(exception ex)
{
//play with the exception.
}
finally
{
if (sqlnamelist != null)
sqlnamelist = null;
if (app != null)
app = null;
}
return sqlservers;
}