How to get current instance name from T-SQL

Question

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

Answer 1

Just found the answer to this SO question (literally inside the question, not any answer):

SELECT @@servername 

returns server_name \ instance if it is not the default instance

 SELECT @@servicename 

returns the name of the instance, even if it is the default value (MSSQLSERVER)

Answer 2

How about this:

 EXECUTE xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQl', @value_name='MSSQLSERVER' 

This will also get the instance name. null means the default instance:

 SELECT SERVERPROPERTY ('InstanceName') 

http://technet.microsoft.com/en-us/library/ms174396.aspx

Answer 3

SELECT @@servername will provide you with data as server/instanceName

To get only instanceName , you must run select @@ServiceName .

Answer 4

I found this:

 EXECUTE xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SOFTWARE\Microsoft\Microsoft SQL Server', @value_name = 'InstalledInstances' 

This will give you a list of all instances installed on your server.

---

The ServerName property of the SERVERPROPERTY and @@SERVERNAME returns similar information. The ServerName property provides the Windows server name and instance name, which together make up a unique server instance. @@SERVERNAME contains the local server name currently configured.

And a Microsoft example for the current server:

 SELECT CONVERT(sysname, SERVERPROPERTY('servername')); 

This scenario is useful when there are multiple instances of SQL Server on a Windows server, and the client needs to open another connection to the same instance that the current connection is using.

Answer 5

Why dwell only on the instance name? You can inventory your SQL Server environment as follows:

 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