SQL Server – List of All Databases with Compatibility Levels

Adding this because I was not able to find this query on the first page of my Google search.  Wanted a list of all the databases in the SQL Server instance with a readable compatibility level for each database.

SELECT
      [Server Name] = @@SERVERNAME,
       name as 'Database Name',
       compatibility_level as 'Compatibility Level',
       [SQL Server Version Compatibility] = CASE sys.databases.compatibility_level
              WHEN '160' THEN 'SQL Server 2022'
              WHEN '150' THEN 'SQL Server 2019'
              WHEN '140' THEN 'SQL Server 2017'
              WHEN '130' THEN 'SQL Server 2016'
              WHEN '120' THEN 'SQL Server 2014'
              WHEN '110' THEN 'SQL Server 2012'
              WHEN '100' THEN 'SQL Server 2008'
              WHEN '90' THEN 'SQL Server 2005'
              WHEN '80' THEN 'SQL Server 2000'
              WHEN '70' THEN 'SQL Server 7.0'
              WHEN '60' THEN 'SQL Server 6.0'
              ELSE Null
END
FROM sys.databases
where name not in ('master','tempdb','model','msdb')
order by name;

2 Comments

Leave a Comment