How To Find Compatibility Level Of All Databases In SQL Server

I was working on STRING_SPLIT function for one of the query building programs and the function seemed not working for me. While checking the internal SQL conditions, I happen to find that the cause was due to the compatibility of the database to the SQL Server.

The list of compatibility levels to the corresponding SQL Server version is showing in the picture below:

Compatibility Level SQL Server 1

Now, I had a very hard time to find out which all databases were on my server with different compatibility levels. I wrote a quite simple query to find that out to get those findings very simple for us.

select name, compatibility_level , version_name =
CASE compatibility_level
WHEN 80  THEN 'SQL Server 2000'
WHEN 90  THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008/R2'
WHEN 110 THEN 'SQL Server 2012'
WHEN 120 THEN 'SQL Server 2014'
WHEN 130 THEN 'SQL Server 2016'
WHEN 140 THEN 'SQL Server 2017'
WHEN 150 THEN 'SQL Server 2019'
ELSE 'new but not known yet - '+CONVERT(varchar(50),compatibility_level)
END
from sys.databases 

Compatibility Level All Databases SQL Server






If you find that the compatibility level of a database is not the actual value recommended by Microsoft but set on your engine, then please follow the syntax to alter the compatibility level of a database.

ALTER DATABASE [your db name]
SET COMPATIBILITY_LEVEL = 140 -- 140 Compatibility level is for SQL Server 2017

Please change the compatibility level of your database as per your SQL Server version using this script.

1 comment:

  1. Added Server 2022 to the Script:

    select name, compatibility_level , version_name =
    CASE compatibility_level
    WHEN 80 THEN 'SQL Server 2000'
    WHEN 90 THEN 'SQL Server 2005'
    WHEN 100 THEN 'SQL Server 2008/R2'
    WHEN 110 THEN 'SQL Server 2012'
    WHEN 120 THEN 'SQL Server 2014'
    WHEN 130 THEN 'SQL Server 2016'
    WHEN 140 THEN 'SQL Server 2017'
    WHEN 150 THEN 'SQL Server 2019'
    WHEN 160 THEN 'SQL Server 2022'
    ELSE 'new but not known yet - '+CONVERT(varchar(50),compatibility_level)
    END
    from sys.databases

    ReplyDelete