CODEWITHSUNDEEP
sql-server
saimadan
saimadan

Reputation: 183

Is there a way to query for the supported compatibility level of a SQL Server?

In the Microsoft documentation here, we have a list of 'Supported Compatibility Level Values' for each SQL Server version. Is this information persisted in SQL Server? If not, is there any other way to fetch this information?

I looked at the Windows registry already, I did not find it.

Upvotes: 5

Views: 2895

Answers (4)

somerg
somerg

Reputation: 31

Reasonable to assume tempdb will have compatibility level of engine ?

SELECT compatibility_level FROM sys.databases WHERE name = 'tempdb';

Upvotes: 3

Martin Cairney
Martin Cairney

Reputation: 1767

Microsoft used to have an approach where each version of SQL Server supported the compatibility level of the current release down to n-2.

Since the release of SQL Server 2016, the previously supported compatibility levels have not been removed (this is intended to assist with the change to certification at the database compatibility level). I'm not completely sure whether 100 will be removed in the next release of SQL Server, but I suspect that it will remain to assist with future upgrade paths.

There is a matrix that shows this pattern.

What this effectively means is that you could query the master database to get the current release compatibility level and then assume that each release down to 100 is also available.

Upvotes: 3

Chris Albert
Chris Albert

Reputation: 2507

Here is a query I use to get basic info on databases from a server. It queries sys.databases. To translate the compatability level you can use this list.

SELECT
    name                     AS [DB Name]
    , state_desc             AS [Status]
    , create_date            AS [Create Date]
    , database_id            AS [DB ID]
    , recovery_model_desc    AS [Recovery Model]
    , SUSER_SNAME(owner_sid) AS [Owner]
    , [compatibility_level]  AS [Compatibility Level]
    , collation_name         AS [Collation]
    , is_auto_close_on       AS [Auto Close]
FROM
    sys.databases
ORDER BY
    name;

Upvotes: 0

Doug Coats
Doug Coats

Reputation: 7107

This should be helpfu and exactly what youre after

    select name, compatibility_level , version_name = 
    CASE compatibility_level
        WHEN 65  THEN 'SQL Server 6.5'
        WHEN 70  THEN 'SQL Server 7.0'
        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 unknown - '+CONVERT(varchar(10),compatibility_level)
    END
    from sys.databases

Upvotes: 1

Related Questions