Compatibility level of databases based on sql server version We need to check for databases that are not in the correct compatibility level on a particular instance. For example, on a SQL Server 2016 instance, if there are databases that are not in compatibility level of 130, we need to list them. Similarly, on a SQL Server 2017, , if there are databases that are not in compatibility level of 140, we need to list them.

Take a look into the below script

SELECT name as 'Database name',
       compatibility_level AS 'Compatibility level',
       @@VERSION as 'SQL Version'          
FROM   sys.databases S
As mentioned in comment, you need to query sys.databases to get the relevant information.

Below would be straight forward query:

select name from sys.databases
where compatibility_level not in(130) -- you can hardcode this based on version of SQL server
and database_id &gt; 4


If you want to generalize it then, you may use below query:

select name from sys.databases
where compatibility_level not in(select compatibility_level from sys.databases where database_id = 1)
and database_id &gt; 4


Above would work fine except in scenario where DB upgrade(in-place) has taken place or if you are working on Azure. You can read about it more here.

Hope above helps.