2

I have a mixed environment of servers from 2000 to 2012, and I'd like to find any databases that have a compatibility level set less than the version of the server it is running on.

Ideally, I'd like to do something like:

SELECT name, compatibility_level
FROM master.sys.databases
WHERE compatibility_level <> SERVERPROPERTY('productcompatibilitylevel')

but that unfortunately doesn't exist. Instead I'm having to do a big CASE statement based on SERVERPROPERTY('productversion'). Any better ways to do this?

2

I found a couple of options that I think will work:

First, you could multiply the first two digits of SERVERPROPERTY('productversion') by 10:

SELECT name, compatibility_level
FROM master.sys.databases
WHERE compatibility_level <>
    10 * convert(int,CONVERT(char(2),SERVERPROPERTY('productversion')))

Or, probably easier, you could just compare to the highest compatibility_level on the server:

SELECT name, compatibility_level
FROM master.sys.databases
WHERE compatibility_level < (SELECT max(compatibility_level) FROM sys.databases)

FYI, you can't just compare to the compatibility_level of MASTER or MODEL, as those can be left lower after an upgrade, or deliberately changed. TempDB, however, I think is always the current version.

  • BTW, both these work only on 2005+. We still have a handful of SQL 2000 servers, but I realized that I don't need to run this on them. – BradC Feb 3 '14 at 20:53

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.