SQL 2005+

Obtaining a SQL Server's Name

by Richard Carr, published at http://www.blackwasp.co.uk/SQLServerName.aspx

In some situations it is necessary to obtain the server and instance names for a SQL Server instance, either for informational purposes or to modify the behaviour of stored procedures. These names can be obtained using configuration functions.

Server and Instance Names

Occasionally you will want to modify the behaviour of a stored procedure, trigger or other script based upon the SQL Server instance in which it is executing. For example, it may be that you want a single code base across all of your servers but that the behaviour needs to be slightly different between test, development and live environments. You could check the server name in this situation using an IF statement and execute different commands accordingly.

@@SERVERNAME

To identify the SQL Server and instance names you can use two configuration functions. The first is @@SERVERNAME. This returns the details in the same format that you might use to connect to the instance. For a simple server using the default instance the server name alone is returned. For a server in a cluster, the returned value is the virtual server's name.

PRINT @@SERVERNAME     -- TESTSERVER

When you are connected to an instance other than the default, the server and instance name are returned, separated by a backslash, as in the sample below. For clusters, the server part is the virtual server name.

PRINT @@SERVERNAME     -- TESTSERVER\SQLEXPRESS

@@SERVICENAME

A second configuration function is available when you only wish to obtain the instance information. This is @@SERVICENAME. The function returns the name as it appears in the Services tool that is built into Windows and you use to start and stop services. For the default instance, the result is always "MSSQLSERVER":

PRINT @@SERVICENAME    -- MSSQLSERVER

For non-default instances the instance name is returned:

PRINT @@SERVICENAME    -- SQLEXPRESS