Recently I upgraded a few SQL instances to SQL 2012. One of the things I overlooked was setting databases to the current compatibility level. Days later, when I realized my omission, I quickly identified the databases. As I recall, [model] and [tempdb] (and maybe [msdb]) were at the current level of 110/SQL 2012. [master] was not. Nor were any of the user databases. This simple query was all it took:
SELECT d.name, d.compatibility_level FROM master.sys.databases d WHERE d.compatibility_level < 110
Naturally, I wondered if there were other databases on other instances that were not at the current compatibility level. But the query above only works on SQL 2012. After some digging, I discovered a correlation between the current (highest available) compatibility level of a SQL version and the "major" version number within the product version. I came up with this query, which I believe is valid for SQL 2005 and above:
DECLARE @MajorVersion INT SELECT @MajorVersion = CAST(LEFT(d.ProductVersion, CHARINDEX('.', d.ProductVersion) - 1) AS INT) FROM ( SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR) AS ProductVersion ) d SELECT d.name, d.compatibility_level FROM master.sys.databases d WHERE d.compatibility_level < @MajorVersion * 10
With a registered server group, I ran the script above and was able to find all the databases in my organization that were not set at the highest available compatibility level.