Tuesday, 14 June 2011

TSQL : Correct Compatibility Levels

I'm always finding databases on sites that were not put into the correct compatibility mode when server migrations/upgrades occurred.
This script sorts them all out at once.
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

adapted from 'Database Compatibility Levels : How to change all at Once' to detect the server version

No comments: