SQL SERVER – TSQL Script to Change the Compatibility Level of all Databases

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!

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter