I’ve just migrated 8 of my demo databases from SQL 2014 to SQL 2017, and I want to change the compatibility level (from 120 to 140) of all those databases via TSQL. Here is a script to do just that.
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 = 140' FROM sys.databases
WHERE compatibility_level = 120
SET @rc = @@ROWCOUNT;
RAISERROR('The COMPATIBILITY_LEVEL of %d databases will be changed to 140', 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
Cheers!
