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.