怎么查看和获取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;

}