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;
Thanks! Just what I was looking for.
You might want to change (SQL Server) 2020 to 2022 ?
Good Catch! Thanks!