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
