In SQL Server, how can I test the instance I'm currently connecting is default or not?

Question

Hi guys,

In SQL Server, how can I test the instance I'm currently connecting is default or not? I mean how can I using TSQL to get this information?

Thanks.

Answer 1

Well you should already know this to be honest by the connection string you are using...

Using 2005 Connection strings you would see;

Standard connection string;

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Instance connection string;

Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True;

Answer 2

You can use Select @@Servername or Select ServerProperty('instancename')

ServerProperty will display NULL if you are on the default instance.

See @@ServerName and ServerProperty for further information