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