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

SQL SERVER - Different Methods to Know COMPATIBILITY LEVEL of a Database compatibilitylevel

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

Here are few related scripts about compatibility levels.

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)

Database Compatible Level, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Alternative to DBCC INPUTBUFFER – sys.dm_exec_input_buffer
Next Post
SQL SERVER – How to Get Updated Link for Code and Database Samples?

Related Posts

Leave a Reply