Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get SQL Server Info - Quick Tip & Script

There are a number of ways to collect basic information about SQL Server, its version, edition and other top-level properties.  What is the build, and current service pack level? What is the instance name vs. the actual server name?  Is it clustered or stand-alone and is it virtual or is it physical? What is the security mode of the server?  Another thing I’m often asked what version of .NET is currently installed on the SQL Server?  Need a quick way to get all this info from one simple script? Read on.

While some of this information you can get from the older, time tested T-SQL command, select @@version, which returns system and build information for the current installation of SQL Server, its output is one long nvarchar string.  See @@Version information on MSDN, by clicking the highlighted link

You would in fact, need to parse the string, in order to derive some of its useful information in readable format, and one that can be output to a column, and thus stored in a table.  You can also use, as is suggested, the SERVERPROPERTY (Transact-SQL) function to retrieve the individual property values.

The SEVERPROPERTY function has been around for several versions now, and have added some useful parameters for SQL Server 2012-2014.  One obvious example, with the release of 2012 is the AlwaysOn features, formerly known as HADR, is IsHadrEnabled.  This new property tells us that AlwaysOn Availability Groups is enabled on this server instance.  Again, this is only available in versions SQL 2012 and higher.

So, when I need a quick way to retrieve this basic data, I run a quick script I put together that uses both SERVERPROPERTY, and @@Version.  I also like to use a batch script, or central management server, to run this against multiple SQL Servers in the environment.  It comes in most handy, when doing inventory, and Microsoft true-ups.  After I collect the data, I can store it, parse it, query it and run reports on it.  I will leave that up to your imagination, though perhaps I’ll do a write-up on this further.

The script uses what I see as the most significant properties for the purposes I mentioned. You certainly can add some of the other properties, and modify the script to your own desires.

The properties  I use here, are available and runs on versions 2005 through 2012 (haven’t tested in on 2014, but will likely work)

The output (for one server here), will look like this:

And now, here is the script:

SET NOCOUNT ON

DECLARE @ver NVARCHAR(128)

DECLARE @majorVersion NVARCHAR(4)

SET @ver = CAST(SERVERPROPERTY('productversion') AS NVARCHAR)

SET @ver = SUBSTRING(@ver,1,CHARINDEX('.',@ver)+1)

SET @majorVersion  = CAST(@ver AS nvarchar)
 

SELECT SERVERPROPERTY('ServerName') AS [ServerName],SERVERPROPERTY('InstanceName') AS [Instance],

SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],

SERVERPROPERTY('ProductVersion') AS [ProductVersion],

            CASE @MajorVersion

WHEN '8.0' THEN 'SQL Server 2000'

WHEN '9.0' THEN 'SQL Server 2005'

WHEN '10.0' THEN 'SQL Server 2008'

WHEN '10.5' THEN 'SQL Server 2008 R2'

WHEN '11.0' THEN 'SQL Server 2012'

WHEN '12.0' THEN 'SQL Server 2014'

END AS 'SQL',

SERVERPROPERTY('ProductLevel') AS [ProductLevel],

SERVERPROPERTY('Edition') AS [Edition],

SERVERPROPERTY ('BuildClrVersion') AS NET,

            CASE SERVERPROPERTY('IsClustered')

                        WHEN 0 THEN 'NO'

                        WHEN 1 THEN 'YES'

            END

            AS [IsClustered],

CASE When CHARINDEX('Hypervisor',@@VERSION)>0    THEN 'VM'

                        ELSE 'PHYSICAL'

            END

            AS [VM_PHYSICAL],

             CASE SERVERPROPERTY('IsIntegratedSecurityOnly')

             WHEN 1 THEN 'WINDOWS AUTHENTICATION ONLY'

             WHEN 0 THEN 'SQL & WINDOWS AUTHENTICATION'

  END AS 'SECURITY MODE'

 

Hope this helps!

 ---------------------------------------------------------------------------------------------

You can and should follow me on twitter: @Pearlknows

http://www.pearlknows.com

We offer a comprehensive performance review and 15-point health check of your SQL Server(s).  Take our HealthySQL challenge - if we don't find anything wrong with your SQL Server, the report to you is FREE!  Contact us at rsp05@pearlknows.com

 

 

 

 

 

 

 

 


Comments

Posted by Tim Radney on 20 July 2014

I am pretty sure that the logic for authentication is backwards.  1 = Windows Only and 0 = SQL & Windows" at least that is how my SQL 2014 is reporting.

Posted by RSP on 21 July 2014

Sheesh, guy is an MVP for <1 month.  ;-P  I updated the script.  Not sure how that got juxtaposed.  The article I reference is correct, as is your observation.  Thx!

Posted by Lee Linares on 28 July 2014

All of my SQL 2005 instances return PHYSICAL even though most are VMs.

I also found some SQL 2008 instances that were returned as PHYSICAL when they were VMs. I was able to fix those by adding this line to your CASE statement:

WHEN CHARINDEX('(VM)',@@VERSION)>0    THEN 'VM'

Thanks for sharing your script. It is very useful.

Lee

Leave a Comment

Please register or log in to leave a comment.