一、查看實例名時可用
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;
}