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