New Horizons Get Schooled Blog

Determine which named instance you’re connected to

SQL ServerProgrammatically 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.

Check out our SQL classes!

Posted on 1/27/11 3:00 PM by Get Schooled in Instance, in Microsoft, in New Horizons Computer Learning Center, in @@SERVERNAME, in SQL, in SQL Server, in T-SQL, in Tips & Tricks

Get Schooled

Written by Get Schooled

Search by Topic

    Lists by Topic

    see all

    Be at the center of it all.

    SUBSCRIBE TO NEWSLETTER

    Knowledge Ninjas

    a web-based service that allows software application end users to access a subject matter expert in real-time via a chat interface to resolve questions or problems they encounter in their specific software.