SQL Server set compatibility level for all server databases


use master;

go

 

 

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

 

— Selecting user database names.

select name as DatabaseName

from sys.sysdatabases

where ([dbid] > 4) and ([name] not like ‘$’)

 

OPEN UserDatabases_CTE_Cursor

DECLARE @dbName varchar(100);


DECLARE @compatQuery varchar(500);

 


 

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

While (@@FETCH_STATUS <> 1)

 

BEGIN

— set database compatibility level

set @compatQuery =  ‘ALTER DATABASE ‘ + @dbName + ‘ SET COMPATIBILITY_LEVEL = 100;’

 

— Print SQL statement

print @compatQuery

 

— Execute compatability script

EXEC (@compatQuery)

 

— Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

END

 

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

GO

— Compatibility level can be 80,90,100 on SQL server 2008/SQL server 2008 R2

— On SQL Server 2012 the allowed values are 90,100,110 

Comments (4)

  1. Papy Normand says:

    When reading your script, i was surprised by your test : where ([dbid] > 4) and ([name] not like '$')

    i suppose dbid > 4 because the values 1,2,3,4 are for the "system databases" like master,tempdb,model and msdb  

    [name] not like 'S'  : is it for the databases related to the Reporting Services ?

    Maybe i am going wrong.If it is the case, please, could you provide me a short explanation ?

    Thanks beforehand.

  2. Yes Papy you are right the aim is just to filter to user databases.

  3. Paul says:

    Ahmed,

    Took advantage of SQL Servers undocumented command sp_MSforeachdb command and simplified it down.   In my case I want to update all the DB's and I know the system db's are already on compatibility 100, so I just update any that weren't already there.

    DECLARE @command varchar(1000)

    SELECT @command = 'USE ? IF (select cmptlevel from master.dbo.sysdatabases where dbid = db_id()) < 100  begin alter database ? set compatibility_level=100 end'

    EXEC sp_MSforeachdb @command

  4. Ravi says:

    can you execute and recheck , I am getting syntax error …

    Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near '='.