SQL Stuff

Change Compatibility Levels For All Databases

Suppose you just restored some databases to a new database server (SQL 2014), and you need to double check and perhaps change the compatibility levels. Here’s some quick logic to get that going fast.

use master;
set nocount on

-- get compatibility levels from existing databases
select
            'database'       = upper(name)
,           'compatibility'  = compatibility_level
from
            sys.databases
where
            database_id > 4
order by
            name asc

-- change compatibility levels to 120 for only those databases that are not set to 120
declare              @change_compatibility_level  varchar(max)
set                  @change_compatibility_level  = ''
select               @change_compatibility_level = @change_compatibility_level +
'alter database [' + name + '] set compatibility_level = 120;' + char(10)
from                sys.databases
where               database_id > 4 and compatibility_level not in ('120')
exec                (@change_compatibility_level)

[end]

Advertisements

Categories: SQL Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s