How to get current instance name from T-SQL

Question

How can I get the SQL Server server and instance name of the current connection, using a T-SQL script?

Answer 1

Just found the answer, in this SO question (literally, inside the question, not any answer):

SELECT @@servername

returns servername\instance as far as this is not the default instance

SELECT @@servicename

returns instance name, even if this is the default (MSSQLSERVER)

Answer 2

How about this:

EXECUTE xp_regread @rootkey='HKEY_LOCAL_MACHINE',
                   @key='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQl',
                   @value_name='MSSQLSERVER'

This will get the instance name as well. null means default instance:

SELECT SERVERPROPERTY ('InstanceName')

http://technet.microsoft.com/en-us/library/ms174396.aspx

Answer 3

SELECT @@servername will give you data as server/instanceName

To get only the instanceName you should run select @@ServiceName query .