Monday, August 17, 2015

Use SERVERPROPERTY to list the SQL Server Instance Name

I received an email from one of my customers this morning -- " How do I get the name of the SQL instance and what version it is, with tSQL? "  I love the easy ones! :-)
This is a quick post of one of the examples I gave to them, using SERVERPROPERTY.  

         SELECT 
                 SERVERPROPERTY('MachineName') [HostName]
                SERVERPROPERTY('ServerName') [SQLServerName]
                 CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'DEFAULT' 
                     ELSE SERVERPROPERTY('InstanceName') END [InstanceName]
                 LEFT(@@Version, CHARINDEX('-', @@version) - 2) [ReleaseName],
                 SERVERPROPERTY('Edition') [Edition],
                 SERVERPROPERTY('ProductVersion') [ProductVersion]

See the CASE on 'InstanceName' -- I'm just doing that to return either the name of the named instance, or 'DEFAULT', if it is not a named instance.

These are the results from one of my v2012 instances:



Please also take a look at MSDN for more details on SERVERPROPERTY:
    https://msdn.microsoft.com/en-us/library/ms174396(v=sql.110).aspx

No comments:

Post a Comment