GetDatabaseVersion – Stringify Compatibility Level


Are you working on lots of databases with different compatibility level and different SQL Server versions? I have this problem almost every day. I have to make my code stable and robust enough to work on different SQL Server versions and on the code must use functionality on databases with different compatibility levels.

I have to check for the database compatibility level before configuring the different performance counter and custom features for my database activity monitor, described in my previous post. I was pretty baffled when I discovered this logical error last week.

As a result, I created a helper function used in my day-to-day work to avoid mixing different compatibility levels.

CREATE FUNCTION [dbo].[GetDatabaseVersion]
(
	@dbname NVARCHAR(100) 
)
RETURNS NVARCHAR(50)
AS
BEGIN
	DECLARE @ver NVARCHAR(5) ;
	DECLARE @returnvalue NVARCHAR(100) ;

	SELECT @ver = compatibility_level
	FROM sys.databases WHERE name = @dbname ;

        SET @returnvalue = 
            CASE 
               WHEN @ver = '80' THEN 'SQL Server 2000'
               WHEN @ver = '90' THEN 'SQL Server 2005'
               WHEN @ver = '100' THEN 'SQL Server 2008 (R2)'
               WHEN @ver = '110' THEN 'SQL Server 2012' 
               WHEN @ver = '120' THEN 'SQL Server 2014'
            END;

	RETURN @returnvalue
END

 

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