Server Discovery

Need a quick and easy way to gather basic information about the SQL Server instance you are on?  Keep this script in your SSMS Template Explorer and the information is only a double-click away.  This script works for SQL Server 2005 and SQL 2008 only because it uses the DMV sys.dm_os_sys_info.  If you are on SQL Server 2000 just highlight the code down to the comment and execute.

SELECT
    @@ServerName                    AS ServerName
    ,Case Left(Convert(Varchar(128),SERVERPROPERTY(‘productversion’)),2)
        When ‘8.’ Then ‘SQL 2000’
        When ‘9.’ Then ‘SQL 2005′
        When ’10’ Then ‘SQL 2008’
        Else ‘Unknown’
        End                                As SQLVersion
    ,SERVERPROPERTY(‘productlevel’)        As ServicePackLevel
    ,SERVERPROPERTY(‘productversion’)        As BuildNumber
    ,SERVERPROPERTY(‘edition’)            As Edition
    ,SERVERPROPERTY(‘LicenseType’)        As LicenseType
    ,SERVERPROPERTY(‘NumLicenses’)        As NumLicenses   
    ,CASE SUBSTRING(@@VERSION,PATINDEX(‘%Windows NT%’,@@VERSION),15)
            WHEN ‘Windows NT 5.0’ THEN ‘Windows 2000’
            WHEN ‘Windows NT 5.1’ THEN ‘Windows XP’
            WHEN ‘Windows NT 5.2’ THEN ‘Windows Server 2003 or Windows XP (64-bit)’
            WHEN ‘Windows NT 6.0’ THEN ‘Windows Server 2008’
            WHEN ‘Windows NT 6.1’ THEN ‘Windows Server 2008 R2 or Windows 7’
            ELSE ‘Unknown’
        END                            AS WindowsVersion
    ,CASE WHEN SERVERPROPERTY(‘IsClustered’) = 1
            THEN ‘Yes’
            ELSE ‘No’
        END AS IsClustered
–For SQL Server 2K run to here
    ,cpu_count                            AS LogicalCPUs
    ,cpu_count / hyperthread_ratio            AS PhysicalCPUs
    ,max_workers_count                    AS Threads
    ,physical_memory_in_bytes/1048576        AS PhysicalMemory_MB
FROM  
    sys.dm_os_sys_info

Here is an example of the result set on SQL Server 2008:

Results

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s