How to check SQL Server Database compatibility level?

On 30 Apr, 2015

MS SQL DBA

By : Charith Silva

No Comments

Sometimes there can be certain older versions of SQL Server databases exist in your current SQL Server instance. If you want to check the compatibility level of all the databases in your current SQL Server instance following script might be helpful.

SELECT name as [Databse Name], compatibility_level AS [Compatibility Level], 
CASE compatibility_level
  WHEN 65 THEN 'SQL Server 6.5'
  WHEN 70 THEN 'SQL Server 7.0'
  WHEN 80 THEN 'SQL Server 2000'
  WHEN 90 THEN 'SQL Server 2005'
  WHEN 100 THEN 'SQL Server 2008/R2'
  WHEN 110 THEN 'SQL Server 2012'
  WHEN 120 THEN 'SQL Server 2014'
END as [SQL Server Version]
FROM sys.databases

SELECT name as [Databse Name], compatibility_level AS [Compatibility Level],

CASE compatibility_level

WHEN 65 THEN 'SQL Server 6.5'

WHEN 70 THEN 'SQL Server 7.0'

WHEN 80 THEN 'SQL Server 2000'

WHEN 90 THEN 'SQL Server 2005'

WHEN 100 THEN 'SQL Server 2008/R2'

WHEN 110 THEN 'SQL Server 2012'

WHEN 120 THEN 'SQL Server 2014'

END as [SQL Server Version]

FROM sys.databases

Previous Post Next Post

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva