DECLARE UserDatabases_CTE_Cursor Cursor
-- Selecting user database names.
select name as DatabaseName
where ([dbid] > 4) and ([name] not like '$')
DECLARE @dbName varchar(100);
DECLARE @compatQuery varchar(500);
Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName
While (@@FETCH_STATUS <> -1)
-- set database compatibility level
set @compatQuery = 'ALTER DATABASE ' + @dbName + ' SET COMPATIBILITY_LEVEL = 100;'
-- Print SQL statement
-- Execute compatability script
-- Get next database
-- 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
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 ?
Yes Papy you are right the aim is just to filter to user databases.
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
can you execute and recheck , I am getting syntax error ...
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.