How to Check a Database’s Compatibility Level in SQL Server using T-SQL

In SQL Server, you can use T-SQL to check the compatibility level of a database. All you need to do is query sys.databases to find the compatibility level for the database in question.

Here’s an example:

SELECT compatibility_level
FROM sys.databases
WHERE name = 'WideWorldImporters';

Result:

compatibility_level
-------------------
130                

This example returns the compatibility level of the WideWorldImporters database.

Return all Databases

You can also return the compatibility level of all databases, simply by removing the WHERE clause (and adding the name column).

Here’s an example:

SELECT name, compatibility_level
FROM sys.databases;

Result:

name                compatibility_level
------------------  -------------------
master              140                
tempdb              140                
model               140                
msdb                140                
Solutions           140                
Music               140                
WideWorldImporters  130                
world               140                

Compatibility Table

Here’s a table outlining the compatibility levels of each product, as well as the compatibility levels that they can be configured for.

ProductDatabase Engine VersionDefault Compatibility Level DesignationSupported Compatibility Level Values
Azure SQL Database16160160, 150, 140, 130, 120, 110, 100
Azure SQL Managed Instance16160160, 150, 140, 130, 120, 110, 100
SQL Server 2022 (16.x)16160160, 150, 140, 130, 120, 110, 100
SQL Server 2019 (15.x)15150150, 140, 130, 120, 110, 100
SQL Server 2017 (14.x)14140140, 130, 120, 110, 100
Azure SQL Database12150150, 140, 130, 120, 110, 100
Azure SQL Database Managed Instance12150150, 140, 130, 120, 110, 100
SQL Server 2016 (13.x)13130130, 120, 110, 100
SQL Server 2014 (12.x)12120120, 110, 100
SQL Server 2012 (11.x)11110110, 100, 90
SQL Server 2008 R210.5100100, 90, 80
SQL Server 200810100100, 90, 80
SQL Server 2005 (9.x)99090, 80
SQL Server 2000 (8.x)88080

Table source: Microsoft