Reviewing The Compatibility Level And Status Of Your Databases

I recently migrated several databases running on SQL Server 2005 Standard Edition to SQL Server 2012 Enterprise edition.  Due to the fact that we had numerous databases running on a single instance, each with its own compatibility concerns I needed an easier way to keep track of the current compatibility level of each database as  we migrated, as opposed to using SQL Management Studio’s GUI to dig into each individual database and manually record the settings.

Fortunately SQL Server provides a simple way to get to this information using the sys.databases Dynamic Management View (DMV).

I’ve included a CASE statement here to return the actual version of SQL Server in the Compatibility Level column.  An example of the output you should expect to see from running this script can be shown below.  As you can see, each database is currently in SQL Server 2012 compatibility mode.

compat_level_output_script

Though not necessary, its good to be familiar with the compatibility values that Microsoft has set for each version, as often this is what you will see when dealing with this in a real world setting.

80 – SQL Server 2000
90 – SQL Server 2005
100 – SQL Server 2008
110 – SQL Server 2012
120 – SQL Server 2014
130 – SQL Server 2016

Additionally, you can change the compatibility level of a database using the following script:

In this example, as I work with Dynamics GP quite a bit, I am altering the compatibility level of the DYNAMICS database and setting it to 110 (SQL Server 2012).