Alter database compatibility on all databases in a SQL Server Instance

What do you think about this?

select
name,
case
when
source_database_id is not null and
is_read_only = 0 and
state = 0
then 'ALTER DATABASE ['+name+'] SET COMPATIBILITY_LEVEL = 130;'
when source_database_id is not null
then '' -- It is a snapshot
when is_read_only = 0
then '-- It should be set to read_write before'
when state = 0
then '-- It sould be set to online before'
else '-- Unknown case'
end as sql_command
from sys.databases
where database_id > 4
and COMPATIBILITY_LEVEL <> 130

Posted by: Zsolt Kovacs | May 14, 2018 9:15:07 AM

Your code did not work for me; however this one did

BEGIN
SET NOCOUNT ON;
DECLARE @rc INT = 0, @i INT = 1, @v_cmd NVARCHAR(MAX);
DECLARE @cmds TABLE(RowNum INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Cmd NVARCHAR(MAX) NOT NULL);
INSERT INTO @cmds
SELECT 'ALTER DATABASE ' + name + ' SET COMPATIBILITY_LEVEL = 150' FROM sys.databases
WHERE compatibility_level = 120
SET @rc = @@ROWCOUNT;
RAISERROR('The COMPATIBILITY_LEVEL of %d databases will be changed to 150 (SQL 2019)', 10, 1, @rc) WITH NOWAIT;
WHILE @i <= @rc
BEGIN
SELECT @v_cmd = cmd FROM @cmds WHERE RowNum = @i;
PRINT @v_cmd;
EXEC(@v_cmd);
SET @i += 1;
END;
END

Posted by: Andrzej | Mar 4, 2022 5:05:55 AM

Thanks, just what I needed.

Posted by: Robin Wilson | Nov 25, 2022 6:23:57 PM

Robin , appreciate the feedback

Posted by: Rambler | Nov 26, 2022 8:59:13 AM