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:
Post a Comment