Getting SQL Server Information

Using TSQ to obtain MS SQL Server Information

By Strictly-Software

In the years gone by when we had relational database systems that worked across MS SQL Versions such as our DEV, DEMO and LIVE databases we sometimes needed to do a check to see what the Version of the server was before running the appropriate code.

Our DEV server might be pretty old compared to our LIVE server and code that would run on SQL 2005 wouldn't on 2000.

An example would be if the Server needed to know whether it could use a CTE and temporary view to return hierarchical information or whether it had to use a more basic stack style temporary table version.

For example the stored proc we would call on any server would be:

EXEC dbo.usp_get_workers_heriarchy

-- inside the stored proc we would have a function wrapped around the @@version code that would just return 2000,2005,2008,2012 etc
DECLARE @SQLVERSION INT
SELECT @SQLVERSION = dbo.udf_GET_SQL_VERSION(@@VERSION) -- returns 2000 or 2012

IF @SQLVERSION > 2005
  BEGIN
        -- use a more modern CTE and temporary view
        EXEC dbo.usp_SQL_GET_WORKERS_CTE_VERSION
  END
ELSE -- handle everything under 2005 when CTE's and temporary views came out
  BEGIN
        -- use old stack method for adjency 
        EXEC dbo.usp_SQL_GET_WORKERS_STACK_VERSION
  END

So if the dbo.udf_GET_SQL_VERSION(@@VERSION) returned 2005, 2008, 2012, 2015 then it would use the most modern type of TSQL to handle returning a hierarchy whilst anything below that would use a stack version.

We would have to build this code up on an SQL system that handled CTES otherwise trying to save the stored procedure and the CTE would just cause invalid SQL errors as it wouldn't recognise the code.

So we built it on an SQL 2005+ machine and then had no problems as all calls were made from the front end by stored procedures and any machine over 2005 would handle a CTE or Stack response.

In the old days when we only had @@VERSION with very little text to parse it was pretty simple to just extract the SQL version from the return string, e.g remove 'Microsoft SQL Server ' or look for the first number after the word Server and LTRIM(RTRIM(@Version)) it to get 2005 such as this example piece of code which would have been used in the UDF dbo.udf_GET_SQL_VERSION in the example above.

DECLARE @Version varchar(30)
SELECT @Version = @@VERSION -- Microsoft SQL Server 2012 (RTM) - 14.0.1000.169 (X64)
SELECT @Version = REPLACE(@VERSION,'Microsoft SQL Server ','')
SELECT @Version = LEFT(@Version,4) -- to get just 2012 not the (RTM) - 14.0.1000.169 (X64)
SELECT @Version = CAST(@Version as INT) -- return as an integer
RETURN @Version -- e.g 2012

Now we have even more information in the @@VERSION system variable. For example now if you call @@VERSION on a later edition you will get.

SELECT  @@version
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
	Feb 10 2012 19:39:15 
	Copyright (c) Microsoft Corporation
	Web Edition (64-bit) on Windows NT 6.2  (Build 9200: ) <x64>

Which as you can see contains much more information than the old @@VERSION which just returned the bare basics of the server. Now we get the build version, the Edition type and Build details.

To access this information you can now use the SERVERPROPERTY function calls to get detailed information from the @@VERSION data but on it's own e.g

SELECT  SERVERPROPERTY ('productversion')
SELECT  SERVERPROPERTY ('productlevel')
SELECT	SERVERPROPERTY ('edition')

Which returns....

11.0.2100.60
RTM
Web Edition (64-bit)

Two other interesting functions are:

SELECT	SERVERPROPERTY ('InstanceDefaultDataPath')
SELECT	SERVERPROPERTY ('InstanceDefaultLogPath')

Which return the paths of your default data and log files locations for the current SQL Server instance e.g

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\

So as we can see using a branch off @@VERSION on any old code you may have lying about may need editing and if you are automating things such as data beign moved and paths being created you can see hopw the DefaultDataPath and DefaultLogPath variables maybe of use. We all live and learn and the world of SQL keeps turning ensuring jobs for people to fix old code!

By Strictly-Software