LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon