SQLDBA- Generate the ALTER script for set compatibility for all databases

 

-- Alter  script set  compatibility y to sql server  2022

DECLARE @DBName SYSNAME;

DECLARE @SQL NVARCHAR(MAX);

-- Table variable to hold database names

DECLARE @DBList TABLE (DBName SYSNAME);

-- Insert all user databases except system ones

INSERT INTO @DBList (DBName)

SELECT name FROM sys.databases

WHERE database_id > 4 -- Exclude system DBs

AND state_desc = 'ONLINE';

-- Initialize loop

WHILE EXISTS (SELECT 1 FROM @DBList)

BEGIN

    -- Get one database name

    SELECT TOP 1 @DBName = DBName FROM @DBList;

    -- Build and execute the ALTER statement

    SET @SQL = 'ALTER DATABASE [' + @DBName + '] SET COMPATIBILITY_LEVEL = 160;';

    PRINT 'Updating compatibility for: ' + @DBName;

PRINT @SQL

    --EXEC sp_executesql @SQL;

    -- Remove processed DB

    DELETE FROM @DBList WHERE DBName = @DBName;

END

--Set  compatability  to  SQL server  2022

 SELECT 'ALTER DATABASE [' +  name+ '] SET COMPATIBILITY_LEVEL = 160

 GO' FROM sys.databases

WHERE name IN ( 

 'master'

)

-- Roll back script

 SELECT 'ALTER DATABASE [' +  name+ '] SET COMPATIBILITY_LEVEL = '+CONVERT(VARCHAR(5),compatibility_level) +'

 GO' FROM sys.databases

WHERE name IN ( 

 'master'

)