Agent Jobs

Find Compatibility Levels And SQL Version Support

Here’s some quick SQL logic that shows the Compatibility_Level with the type of support from the different SQL Versions out there.



--find all compatibility levels per database and see what other SQL versions support it.

use master;
set nocount on

select
	'server_name'		= @@servername
,	'database_name'		= upper(sd.name)
,	'compatability_level'	=
				case cast(sd.compatibility_level as varchar(255))
					when 60     then '60 (SQL 6.0)		Works only on SQL 6'
					when 65     then '65 (SQL 6.5)		Works only on SQL 6.5'
					when 70     then '70 (SQL 7.0)		Works only on SQL 7'
					when 80     then '80 (SQL 2000)		Works only on SQL 2000'
					when 90     then '90 (SQL 2005)		Works on SQL 2005, 2000'
					when 100    then '100 (SQL 2008)	Works on SQL 2008R2, 2005'
					when 110    then '110 (SQL 2012)	Works on SQL 2012, 2008R2, 2005'
					when 120    then '120 (SQL 2014)	Works on SQL 2014, 2012, 2008R2'
					when 130    then '130 (SQL 2016)	Works on SQL 2016, 2014, 2012, 2008R2'
					when 140    then '140 (SQL 2017)	Works on SQL 2017, 2016, 2014, 2012, 2008R2'
				end
from
	sys.databases sd
where
	database_id <> 2
order by
	sd.name
,	sd.compatibility_level desc

Feel free to check Microsoft Documentation to verify found here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level

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