1

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
  • 1
    Have you tried anything from your side running against sys.databases? – Learning_DBAdmin Nov 11 '19 at 5:36
  • query sys.databases to get the information – Squirrel Nov 11 '19 at 5:36
2

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 > 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 > 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.

| improve this answer | |
  • 1
    Adding the that generalized version might not work with Azure SQL Database too. – Dan Guzman Nov 11 '19 at 10:56
  • Edited my answer, Thank you. – Learning_DBAdmin Nov 11 '19 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.