blocks|key|264261|text|只是在this+SO+question中找到了答案(字面意思是在问题中，而不是任何答案)：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|264262|SELECT+@@servername|code-block|syntax|javascript|264263|返回servername\instance，只要这不是默认实例|264264|SELECT+@@servicename|264265|返回实例名称，即使这是默认值(MSSQLSERVER)|264266|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/129861/how-can-i-query-the-name-of-the-current-sql-server-database-instance^0|3|G|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|11|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|12|8|@]|9|@]|C|$G|H]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|C|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]]]

Just found the answer, in <a href="https://stackoverflow.com/questions/129861/how-can-i-query-the-name-of-the-current-sql-server-database-instance">this SO question</a> (literally, inside the question, not any answer):

<pre><code>SELECT @@servername
</code></pre>

returns servername\instance as far as this is not the default instance

<pre><code>SELECT @@servicename
</code></pre>

returns instance name, even if this is the default (MSSQLSERVER)

blocks|key|47659|text|这样如何：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|47660|EXECUTE+xp_regread+@rootkey='HKEY_LOCAL_MACHINE',
+++++++++++++++++++@key='SOFTWARE\Microsoft\Microsoft+SQL+Server\Instance+Names\SQl',
+++++++++++++++++++@value_name='MSSQLSERVER'|code-block|syntax|javascript|47661|这也将获得实例名称。null表示默认实例：|offset|length|style|CODE|47662|SELECT+SERVERPROPERTY+('InstanceName')|47663|http://technet.microsoft.com/en-us/library/ms174396.aspx|47664|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|A|4|0|0|0|1K|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Z|8|@$I|10|J|11|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|12|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|13|8|@]|9|@$I|14|J|15|1|16]]|A|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|P]]]]

How about this:

<pre><code>EXECUTE xp_regread @rootkey='HKEY_LOCAL_MACHINE',
 @key='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQl',
 @value_name='MSSQLSERVER'
</code></pre>

This will get the instance name as well. <code>null</code> means default instance:

<pre><code>SELECT SERVERPROPERTY ('InstanceName')
</code></pre>

<a href="http://technet.microsoft.com/en-us/library/ms174396.aspx" rel="noreferrer">http://technet.microsoft.com/en-us/library/ms174396.aspx</a>

blocks|key|47731|text|SELECT+@@servername将为您提供server/instanceName格式的数据|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|47732|要只获取查询，您应该运行select+@@ServiceName+instanceName。|47733|entityMap^0|0|J|O|J|0|C|K|X|C|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|I|$]]

<code>SELECT @@servername</code> will give you data as <code>server/instanceName</code>

To get only the <code>instanceName</code> you should run <code>select @@ServiceName</code> query .

blocks|key|47766|text|为什么只停留在实例名称上？您可以通过以下方式清点SQL+Server环境：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|47767|SELECT++
++++SERVERPROPERTY('ServerName')+AS+ServerName,++
++++SERVERPROPERTY('MachineName')+AS+MachineName,
++++CASE+
++++++++WHEN++SERVERPROPERTY('InstanceName')+IS+NULL+THEN+''
++++++++ELSE+SERVERPROPERTY('InstanceName')
++++END+AS+InstanceName,
++++''+as+Port,+--need+to+update+to+strip+from+Servername.+Note:+Assumes+Registered+Server+is+named+with+Port
++++SUBSTRING+(+(SELECT+@@VERSION),1,+CHARINDEX('-',(SELECT+@@VERSION))-1+)+as+ProductName,
++++SERVERPROPERTY('ProductVersion')+AS+ProductVersion,++
++++SERVERPROPERTY('ProductLevel')+AS+ProductLevel,
++++SERVERPROPERTY('ProductMajorVersion')+AS+ProductMajorVersion,
++++SERVERPROPERTY('ProductMinorVersion')+AS+ProductMinorVersion,
++++SERVERPROPERTY('ProductBuild')+AS+ProductBuild,
++++SERVERPROPERTY('Edition')+AS+Edition,
++++CASE+SERVERPROPERTY('EngineEdition')
++++++++WHEN+1+THEN+'PERSONAL'
++++++++WHEN+2+THEN+'STANDARD'
++++++++WHEN+3+THEN+'ENTERPRISE'
++++++++WHEN+4+THEN+'EXPRESS'
++++++++WHEN+5+THEN+'SQL+DATABASE'
++++++++WHEN+6+THEN+'SQL+DATAWAREHOUSE'
++++END+AS+EngineEdition,++
++++CASE+SERVERPROPERTY('IsHadrEnabled')
++++++++WHEN+0+THEN+'The+Always+On+Availability+Groups+feature+is+disabled'
++++++++WHEN+1+THEN+'The+Always+On+Availability+Groups+feature+is+enabled'
++++++++ELSE+'Not+applicable'
++++END+AS+HadrEnabled,
++++CASE+SERVERPROPERTY('HadrManagerStatus')
++++++++WHEN+0+THEN+'Not+started,+pending+communication'
++++++++WHEN+1+THEN+'Started+and+running'
++++++++WHEN+2+THEN+'Not+started+and+failed'
++++++++ELSE+'Not+applicable'
++++END+AS+HadrManagerStatus,
++++CASE+SERVERPROPERTY('IsSingleUser')+WHEN+0+THEN+'No'+ELSE+'Yes'+END+AS+InSingleUserMode,
++++CASE+SERVERPROPERTY('IsClustered')
++++++++WHEN+1+THEN+'Clustered'
++++++++WHEN+0+THEN+'Not+Clustered'
++++++++ELSE+'Not+applicable'
++++END+AS+IsClustered,
++++''+as+ServerEnvironment,
++++''+as+ServerStatus,
++++''+as+Comments|code-block|syntax|javascript|47768|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Why stop at just the instance name? You can inventory your SQL Server environment with following:

<pre><code>SELECT 
 SERVERPROPERTY('ServerName') AS ServerName, 
 SERVERPROPERTY('MachineName') AS MachineName,
 CASE 
 WHEN SERVERPROPERTY('InstanceName') IS NULL THEN ''
 ELSE SERVERPROPERTY('InstanceName')
 END AS InstanceName,
 '' as Port, --need to update to strip from Servername. Note: Assumes Registered Server is named with Port
 SUBSTRING ( (SELECT @@VERSION),1, CHARINDEX('-',(SELECT @@VERSION))-1 ) as ProductName,
 SERVERPROPERTY('ProductVersion') AS ProductVersion, 
 SERVERPROPERTY('ProductLevel') AS ProductLevel,
 SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion,
 SERVERPROPERTY('ProductMinorVersion') AS ProductMinorVersion,
 SERVERPROPERTY('ProductBuild') AS ProductBuild,
 SERVERPROPERTY('Edition') AS Edition,
 CASE SERVERPROPERTY('EngineEdition')
 WHEN 1 THEN 'PERSONAL'
 WHEN 2 THEN 'STANDARD'
 WHEN 3 THEN 'ENTERPRISE'
 WHEN 4 THEN 'EXPRESS'
 WHEN 5 THEN 'SQL DATABASE'
 WHEN 6 THEN 'SQL DATAWAREHOUSE'
 END AS EngineEdition, 
 CASE SERVERPROPERTY('IsHadrEnabled')
 WHEN 0 THEN 'The Always On Availability Groups feature is disabled'
 WHEN 1 THEN 'The Always On Availability Groups feature is enabled'
 ELSE 'Not applicable'
 END AS HadrEnabled,
 CASE SERVERPROPERTY('HadrManagerStatus')
 WHEN 0 THEN 'Not started, pending communication'
 WHEN 1 THEN 'Started and running'
 WHEN 2 THEN 'Not started and failed'
 ELSE 'Not applicable'
 END AS HadrManagerStatus,
 CASE SERVERPROPERTY('IsSingleUser') WHEN 0 THEN 'No' ELSE 'Yes' END AS InSingleUserMode,
 CASE SERVERPROPERTY('IsClustered')
 WHEN 1 THEN 'Clustered'
 WHEN 0 THEN 'Not Clustered'
 ELSE 'Not applicable'
 END AS IsClustered,
 '' as ServerEnvironment,
 '' as ServerStatus,
 '' as Comments
</code></pre>

blocks|key|264468|text|要获取您连接的服务器和实例列表，请执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|264469|select+*+from+Sys.Servers|code-block|syntax|javascript|264470|要获取连接的服务器所具有的数据库列表，请执行以下操作：|264471|SELECT+*+from+sys.databases;|264472|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

To get the list of server and instance that you're connected to:

<pre><code>select * from Sys.Servers
</code></pre>

To get the list of databases that connected server has:

<pre><code>SELECT * from sys.databases;
</code></pre>

blocks|key|264436|text|只是为了给注册表查询添加一些说明。它们仅列出当前实例的匹配位(32或64)的实例。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|264437|64位操作系统上32位SQL实例的实际注册表项为：|264438|HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft+SQL+Server|code-block|syntax|javascript|264439|您可以在64位实例上进行查询，以获得所有32位实例。32位实例似乎仅限于Wow6432Node，因此无法读取64位注册表树。|264440|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Just to add some clarification to the registry queries. They only list the instances of the matching bitness (32 or 64) for the current instance.

The actual registry key for 32-bit SQL instances on a 64-bit OS is:

<pre><code>HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server
</code></pre>

You can query this on a 64-bit instance to get all 32-bit instances as well. The 32-bit instance seems restricted to the Wow6432Node so cannot read the 64-bit registry tree.

blocks|key|47797|text|另一种查找实例名称的方法-在数据库名称上单击鼠标右键并选择属性，在该部分中，您可以看到左下角的连接属性，单击它，您可以看到实例名称。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|47798|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

another method to find Instance name- Right clck on Database name and select Properties, in this part you can see view connection properties in left down corner, click that then you can see the Instance name.

How can I get the SQL Server server and instance name of the current connection, using a T-SQL script?

How to get current instance name from T-SQL

SQL Server

数据库

服务器

如何使用T-SQL脚本获取当前连接的SQL Server服务器和实例名称？

问如何从T-SQL中获取当前实例名
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从T-SQL中获取当前实例名EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从T-SQL中获取当前实例名
EN