Below script simply gets all databases that don't have a latest compatibility level and then changes the level to latest. I am sure there could be some error handling code written in that, but it kinda does the job as it is.
DECLARE @ServerVersion INT
SELECT @ServerVersion = 10 * (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))
-- loop databases setting compatibility mode correctly
DECLARE GET_DATABASES CURSOR
READ_ONLY
FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != CAST(@ServerVersion AS VARCHAR(10))
DECLARE @DATABASENAME NVARCHAR(255)
DECLARE @COUNTER INT
SET @COUNTER = 1
OPEN GET_DATABASES
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- change database compatibility
EXECUTE sp_dbcmptlevel @DATABASENAME , @ServerVersion
PRINT @DATABASENAME + ' changed'
SET @COUNTER = @COUNTER + 1
END
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
END
CLOSE GET_DATABASES
DEALLOCATE GET_DATABASES
DECLARE @ServerVersion INT
SELECT @ServerVersion = 10 * (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))
-- loop databases setting compatibility mode correctly
DECLARE GET_DATABASES CURSOR
READ_ONLY
FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != CAST(@ServerVersion AS VARCHAR(10))
DECLARE @DATABASENAME NVARCHAR(255)
DECLARE @COUNTER INT
SET @COUNTER = 1
OPEN GET_DATABASES
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- change database compatibility
EXECUTE sp_dbcmptlevel @DATABASENAME , @ServerVersion
PRINT @DATABASENAME + ' changed'
SET @COUNTER = @COUNTER + 1
END
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
END
CLOSE GET_DATABASES
DEALLOCATE GET_DATABASES
Thank you share
ReplyDelete