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)