Programmatically determine which named instance you’re connected to (SQL Server 2000/2005)

If you’re using SQL Server named instances, you may wonder how to determine - from inside a stored procedure, for example - which instances you’re connected to at any given time. Actually, there are two ways, and both are quite simple.

First, you can use the system-supplied global variable @@SERVERNAME. Go ahead and connect to a named instance, then try the following statement:

SELECT InstanceName = @@SERVERNAME

As you’ll observe, the result consists of the physical server name, followed by a backslash, followed by the name of the instance. It’s also a simple matter to parse out just the instance name, using the available T-SQL string functions, if you need to.

The other, and essentially equivalent, method is to use the built-in SERVERPROPERTY function. Here’s an example, which should produce the same results as our previous demonstration:

SELECT InstanceName = SERVERPROPERTY('ServerName')

For more information on these two options, see the appropriate entries in SQL Server Books Online.