SQL SERVER – Different Methods to Know COMPATIBILITY LEVEL of a Database

COMPATIBILITY LEVEL in SQL Server indicates the version of SQL Server with which the database is to be made compatible. Whenever you create a new database, compatibility level is set to that database. Sometimes you may need to change this so that some code will work.

There are many methods to know the compatibility levels of a database.

Let us create this database

CREATE DATABASE testing

Now run any of these codes to know the compatibility levels of the newly created database named testing

1) Use system stored procedure sp_helpdb

EXEC sp_helpdb testing

It shows you two results. The first result has the column named compatibility_level that shows the value.

2) Use system stored procedure sp_dbcmptlevel

EXEC sp_dbcmptlevel testing

The result is

The current compatibility level is 120.

3) Use system views

SELECT name,compatibility_level
FROM sys.databases
WHERE name='testing'

The result is

Depends on the version you use will get different value when you execute the above scripts.

Here are few related scripts about compatibility levels.

• SQL SERVER – How to Change Database Compatibility Level?
• SQL SERVER – ERROR: FIX – Database diagram support objects cannot be installed
• SQL SERVER – Fix: Error: Compatibility Level Drop Down is Empty

Let me know which one of the above is your favorite method to find details about SQL Server’s version.

Reference: Pinal Dave (https://blog.sqlauthority.com)