Search

Monday, May 28, 2012

Change all databases compatibility level at once

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  

1 comment: