SQL Server: set all databases to compatibility level 150
Generate the ALTER DATABASE statements that set user databases to compatibility level 150.
Generate the ALTER DATABASE statements that set every user database with a compatibility level below 150 (SQL Server 2019) to that level. The statements are only displayed: review them before running them.
Generate the statements
SELECT [name],
'ALTER DATABASE [' + [name] + '] SET COMPATIBILITY_LEVEL = 150;' as setCompatibilityLevel
FROM sys.databases
-- only those that are less than a 150 compat level
WHERE compatibility_level < 150
-- don't do the master, model, tempdb, msdb
AND database_id > 4;
Run
Copy the generated statements and run them.
Notes
- A change of compatibility level can change query plans and some behaviours: test the application before applying it in production.
database_id > 4excludes the system databases (master,tempdb,model,msdb).