Search

Showing posts with label Change all databases compatibility level at once. Show all posts
Showing posts with label Change all databases compatibility level at once. Show all posts

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