Changing Compatibility Level to all user databases

DECLARE @DatabaseName NVARCHAR(128)

DECLARE @SQL NVARCHAR(500)

 

-- Cursor to iterate through all databases

DECLARE db_cursor CURSOR FOR

SELECT name

FROM sys.databases

WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') -- Exclude system databases

 

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DatabaseName

 

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Create the dynamic SQL to change the compatibility level

    SET @SQL = 'ALTER DATABASE [' + @DatabaseName + '] SET COMPATIBILITY_LEVEL = 150;'

   

    -- Execute the dynamic SQL

    EXEC sp_executesql @SQL

   

    -- Print the action (for logging purposes)

    PRINT 'Changed compatibility level to 2019 for database: ' + @DatabaseName

 

    -- Fetch the next database

    FETCH NEXT FROM db_cursor INTO @DatabaseName

END

 

-- Close and deallocate the cursor

CLOSE db_cursor

DEALLOCATE db_cursor