SQL Server – Check the compatibility level of a database

Varinder Sandhu SQL, SQL-Advanced, SQL-Tips and Tricks, Varinder Sandhu

You can check the compatibility of a database simply by running the below sql command.

sp_dbcmptlevel 'Database_Name'

Read more about sp_dbcmptlevel

Values of compatibility levels and their corresponding MS SQL Server versions

60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

The values 60 and 65 are deprecated and will be removed in a SQL 2008 and later.

You can find the list of databases and their compatibility levels with simple script as:

SELECT name AS DatabaseName,
CompatibilityLevel =  
 CASE 
         WHEN cmptlevel = 60 THEN 'SQL Server 6.0'
         WHEN cmptlevel = 65 THEN 'SQL Server 6.5'
         WHEN cmptlevel = 70 THEN 'SQL Server 7.0'
         WHEN cmptlevel = 80 Then 'SQL Server 2000'
         WHEN cmptlevel = 90 then 'SQL Server 2005'
         ELSE 'SQL Server 2008' 
      END 
FROM sysdatabases
WHERE name not in ('master','tempdb','model','msdb')
ORDER BY name