How to get current instance name from T-SQL

Practical applications in different configurations

Named vs default instances

How many hairs does a rabbit have? No idea? Don't worry, SQL Server sometimes can't tell the difference between default and named instances either:

• Default Instance: Has no name. Listen on default port, 1433.
• Named Instance: Has a wonderful moniker.

This query tells you whether it's a named or default instance:

-- Say my name
SELECT SERVERPROPERTY('InstanceName') AS InstanceName;


Handling clusters and always-on availability groups

In a clustered or high-availability environment, @@SERVERNAME may not show the current active node. In an Always On Availability Group, @@SERVICENAME might be more informative:

-- I feel clustered today.
SELECT 
  CASE 
    WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'Clustered Instance'
    WHEN SERVERPROPERTY('IsHadrEnabled') = 1 THEN 'Always On Availability Group'
    ELSE SERVERPROPERTY('InstanceName')
  END AS InstanceType;


Version and permission concerns

Before you plunge head-first into these queries, be aware:

1. You need appropriate permissions. We don't want any 007 moments here.
2. SQL Server keeps growing — keep your scripts updated.

Especially in the future SQL Server may include port details too:

-- The future is now, old man!
SELECT 
  SERVERPROPERTY('InstanceName') AS InstanceName, 
  SERVERPROPERTY('Port') AS InstancePort;


Deep diving into server details

Reading from the Windows Registry

The SQL Server instance name is stored in the Windows registry:

-- The truth is in there... somewhere.
EXEC xp_regread 
  'HKEY_LOCAL_MACHINE', 
  'SOFTWARE\Microsoft\MSSQLServer\Setup', 
  'SQLPath';


You need sysadmin rights for this. With great power comes great responsibility!

Extracting details from the Server

You can extract a detailed overview of the SQL Server instance with the following command:

-- I want to know everything!
SELECT 
  @@SERVERNAME AS ServerName,
  SERVERPROPERTY('ProductVersion') AS ProductVersion,
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('MachineName') AS MachineName,
  SERVERPROPERTY('IsClustered') AS IsClustered,
  SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled,
  SERVERPROPERTY('ServerName') AS OriginalServerName;


Working with the command line (sqlcmd)

Why settle for SQL Server Management Studio when you can use the command line?

-- They see me typing, They hating
sqlcmd -S servername\instancename


The -S option specifies the server/instance to connect to.