How to get current instance name from T-SQL

Question

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

The accepted answer is correct. `SELECT @@SERVERNAME` produces the result needed to connect using `sqlcmd -S`. If it is the default MSSQLSERVER instance, then it must -not- be specified in the -S parameter. This is on 2017 14.0.2002.14 Developer edition, 64-bit. — lit, Jan 05 '19 at 22:49

score 203 · Accepted Answer · edited May 23 '17 at 12:18

203

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

SELECT @@servername

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

SELECT @@servicename

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

edited May 23 '17 at 12:18

Community

1
1

answered Aug 06 '13 at 01:11

Guillermo Gutiérrez

17,273
17
89
116

1

@blasto, that seems to be the behavior when the instance is the default at the server. Try with a named instance. Check this: http://technet.microsoft.com/en-us/library/ms187944.aspx – Guillermo Gutiérrez Mar 05 '14 at 20:22
11

Incorrect, use @@servername can give you a wrong answer. SELECT CONVERT(sysname, SERVERPROPERTY('servername')) is the correct answer. The @@ServerName reports the SQL cluster name, while serverproperty('servername') reports the Windows cluster name. You need the Windows cluster name to connect to your db (The Windows cluster name can be different from the SQL cluster name; This usually happens when you install a new sql-server version on a different machine (@@servername = Environment.MachineName) and want to keep the old name so you don't have to change all configs). – Stefan Steiger Dec 04 '15 at 10:45
1

@StefanSteiger The linked SO question's accepted answer indicates the solution you presented, but the comments indicate that it does not work for some people. – Trisped Jan 12 '16 at 19:04

score 18 · Answer 2 · answered Aug 06 '13 at 01:03

18

How about this:

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

This will get the instance name as well. null means default instance:

SELECT SERVERPROPERTY ('InstanceName')

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

answered Aug 06 '13 at 01:03

Beno

4,633
1
27
26

10

SELECT SERVERPROPERTY ('InstanceName') gives me a NULL. – Steam Mar 03 '14 at 02:50
4

instead use SELECT CONVERT(sysname, SERVERPROPERTY('servername')); – Stefan Steiger Dec 04 '15 at 10:42
1

@Steam: Probably because it is the default-instance. – Stefan Steiger Dec 04 '15 at 10:43

Shirishkumar Bari · Answer 3 · 2015-05-19T04:18:45.887

11

SELECT @@servername will give you data as server/instanceName

To get only the instanceName you should run select @@ServiceName query .

edited May 19 '15 at 04:18

answered May 18 '15 at 10:49

Shirishkumar Bari

2,692
1
28
36

1

First, you have the slash the wrong way. The correct form is "server\instance". Second, it is not always true that @@servername will give you both the server AND the instance because when there is only a single instance it will return just "server". – Robino Jan 30 '17 at 15:45

Nate S. · Answer 4 · 2017-05-25T21:22:49.283

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

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

Good Stuff Nate, I have a similar more enhanced Inventory script doing the same. contact me for a copy by email... Script would be out of focus for this thread. — Hank Freeman, May 26 '18 at 07:52

score 9 · Answer 5 · answered Jun 07 '15 at 04:39

I found this:

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

That will give you list of all instances installed in your server.

The ServerName property of the SERVERPROPERTY function and @@SERVERNAME return similar information. The ServerName property provides the Windows server and instance name that together make up the unique server instance. @@SERVERNAME provides the currently configured local server name.

And Microsoft example for current server is:

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

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

SELECT CONVERT(sysname, SERVERPROPERTY('servername')); is the correct answer — Stefan Steiger, Dec 04 '15 at 10:42

score 3 · Answer 6 · edited Feb 29 '20 at 00:35

3

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

select * from Sys.Servers

To get the list of databases that connected server has:

SELECT * from sys.databases;

edited Feb 29 '20 at 00:35

Eliot K

614
5
16

answered Feb 28 '20 at 21:05

Saravanan Andavar

49
3

score 2 · Answer 7 · edited Feb 09 '23 at 05:18

You can get your server name, machine name and instance name with Transact-SQL(T-SQL) as shown below:

SELECT @@SERVERNAME                    -- DESKTOP-OVPADTC\SQLEXPRESS
SELECT SERVERPROPERTY ('ServerName')   -- DESKTOP-OVPADTC\SQLEXPRESS

SELECT HOST_NAME()                     -- DESKTOP-OVPADTC
SELECT SERVERPROPERTY ('MachineName')  -- DESKTOP-OVPADTC

SELECT @@SERVICENAME                   -- SQLEXPRESS
SELECT SERVERPROPERTY ('InstanceName') -- SQLEXPRESS

score 1 · Answer 8 · answered Aug 06 '19 at 03:47

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:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server

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.

score 0 · Answer 9 · answered Jul 05 '18 at 09:19

0

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.

answered Jul 05 '18 at 09:19

Yasin

5
4

How to get current instance name from T-SQL

9 Answers9

Linked