Writing a SQL Script for “all versions” of SQL

One of our Microsoft SQL Server consultants gives us a handy tip for writing a SQL script for many different versions of SQL Server.

"One of the daily challenges you may have as a Microsoft DBA is supporting many different versions of SQL Server. One thing that can be particularly painful is writing a script that can be run on all versions of SQL Server past and present. Newer versions of SQL server have new ways on which to find out information or run tasks (that are a lot easier), things which were never there in previous versions.

For example, I notice that a lot of my SQL Severs have been hungry hippos overnight and the Log files on most of my servers are suddenly very large. If I have a 100 SQL severs, I don’t want to be checking each individually, I want to deploy a script across all of them and I need to do it quickly.

I start writing this on my new SQL Server 2012 box and quickly decide to use the Dynamic Management Views (DMV’s). I start with something like:

-- using DMV os Performance stats
SELECT instance_name
,cntr_value 'Log File(s) Used Size (KB)'
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log File(s) Used Size (KB)'

This runs well on my SQL Server 2012, 2008r2 and SQL 2005 servers. But the pesky SQL Server 2000 server simply comes back with “object not found”. Of course, SQL Server 2000 didn’t have DMV’s, man we should really upgrade these. However in the meantime, I need that data. Ok so in 2000 days, I have to use the DBCC command SQLPERF to get the log size.

-- using DBCC SQLPERF(LOGSPACE);

-- Create a temp table
create table #dbccresults
(
DBName [varchar](255),
LogSize_MB decimal(18, 8),
LogSpaceUsed decimal(18, 8),
status int)

-- Get the Log file sizes
insert into #dbccresults
exec ('dbcc sqlperf(logspace)')

-- Output the results
select * from #dbccresults

But is there some way I can have 1 script that does both of these – now that I have both scripts ready?
One trick that we at dsp use is to write our scripts to ensure they check the version of SQL Server first, and then run the relevant piece of SQL based on the version of SQL that part of the script supports.

To do this we use SERVERPROPERTY this is supported in all version of SQL and likely to be ever more.

SELECT CAST(LEFT(SERVERPROPERTY ('productversion'), 2) as int) as ‘ProductVersion’

This will output the following based on the current version of SQL Server:

8 – SQL Server 2000
9 – SQL Server 2005
10 – SQL Server 2008
11 – SQL Server 2012

If we use this in our script with an IF statement then we can allow our DMV script to work on SQL Server 2005 onwards and our DBCC script to run on SQL Server 2000 servers.

-- Run if SQL Server 2000
IF SELECT CAST(LEFT(SERVERPROPERTY ('productversion'), 2) as int) = 8
BEGIN
-- using DBCC SQLPERF(LOGSPACE);

-- Create a temp table
create table #dbccresults
(
DBName [varchar](255),
LogSize_MB decimal(18, 8),
LogSpaceUsed decimal(18, 8),
status int)

-- Get the Log file sizes
insert into #dbccresults
exec ('dbcc sqlperf(logspace)')

-- Output the results
select * from #dbccresults

END

-- Run if SQL Server 2005 or above
IF SELECT CAST(LEFT(SERVERPROPERTY ('productversion'), 2) as int) = 8
BEGIN
-- using DMV os Performance stats
SELECT instance_name
,cntr_value 'Log File(s) Used Size (KB)'
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log File(s) Used Size (KB)'
END

Great, now I have one script I can use anywhere on my system and it’s going to give me the results I need no matter what version of SQL Server I am using.
You can use this method to do any number of checks, and it’s always there in the kitbag for when needed."