Seeing Compatibility Levels of All Databases in SQL Server

Hello to everyone,

In this article, I will give information about how to see the Compatibility Levels of all databases in SQL Server.

You may want to see the Compatibility Levels of all databases before and after upgrades in SQL Server.

You can easily do this using the code below.

SELECT name,
       compatibility_level,
       'ALTER DATABASE [' + name + '] SET COMPATIBILITY_LEVEL = 130;' script,
       ----- Dilerseniz 130 ya da farklı bir version set edilebilirsiniz. Üst kısımdaki 130 sayısını değiştirmeniz yeterli.
       version_name = 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'
                          WHEN 130 THEN
                              'SQL Server 2016'
                          WHEN 140 THEN
                              'SQL Server 2017'
                          WHEN 150 THEN
                              'SQL Server 2019'
                      END
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

When you run the above query, you will see a result similar to the one below.

As you can see, the Compatibility Levels of all databases are listed.

Good luck to everyone in business and life.