Code to Create Compatibility Levels of All Databases in SQL Server

Hello everyone,

In this article, I will provide information on how to generate the code to create the Compatibility Levels of all databases in SQL Server.

In SQL Server, you may want to create the Compatibility Levels of all databases at once before and after the upgrade operations.

You can easily do this using the code below.

DECLARE @QUERY VARCHAR(MAX) = '';
SELECT @QUERY += 'ALTER DATABASE [' + name + '] SET COMPATIBILITY_LEVEL = 130;' + CHAR(10)
FROM sys.databases
WHERE database_id > 4
      AND compatibility_level <> 130
ORDER BY name;
--EXEC (@QUERY) --If you open this section and turn off print, the query works for all databases. So use it carefully.
PRINT (@QUERY);

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

As you can see, the code for creating the Compatibility Levels of all databases is listed.

Good luck to everyone in business and life.