Monday, November 23, 2015

Change compatibility level



Using below statements you can change comparability level of  all user databases  with a single click. Comparability level is decided bases on sql server product level.

SQL Server
Comparability Level
SQL Version
SQL 2016
130

SQL 2014
120
12
SQL 2012
110
11
SQL 2008
100
10
SQL 2005
90
9
SQL 2000
80
8


use master;
go

DECLARE @SQLVer varchar(10);
-- select the compatibility level based in sql server version
select @SQLVer = CASE  SUBSTRING(convert(varchar,SERVERPROPERTY('productversion')),1,2)
when 12 then '120'
when 11 then '110'
when 10 then '100'
when 9. then '90'
when 8. then '80'
end


DECLARE UserDatabases_CTE_Cursor Cursor
FOR

-- filer user database names from sysdatabases
select name as DatabaseName from sys.sysdatabases where ([dbid] > 4)

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 = ' + @SQLVer



-- 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