Check All Databases’ Compatibility Level and Modify Them


You can use this query to find out what your databases’ compatibility levels are:
select name, compatibility_level from sys.databases
You can update the compatibility level of these databases in one shot either through a cursor like this:
http://blogs.msdn.com/b/ai/archive/2012/06/12/sql-server-set-comparability-level-for-all-server-databases.aspx

You can still use the unsupport sp_msforeachdb to make the change:



sp_msforeachdb ' USE [?] IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' AND ''?'' <> ''distribution''
 BEGIN  ALTER  DATABASE [?] SET COMPATIBILITY_LEVEL = 100; END '

-- 80 = SQL Server 2000
--90 = SQL Server 2005
--100 = SQL Server 2008/R2
--110 = SQL Server 2012
--120 = SQL Server 2014 --not tested


 --Check all databases' compatibility level for your instance
 select name, compatibility_level from sys.databases


Advertisements


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