{"id":2040,"date":"2022-02-12T20:03:24","date_gmt":"2022-02-12T20:03:24","guid":{"rendered":"https:\/\/www.theSQLReport.com\/?p=2040"},"modified":"2023-06-05T09:25:52","modified_gmt":"2023-06-05T09:25:52","slug":"sql-server-list-of-databases-with-compatibility-levels","status":"publish","type":"post","link":"http:\/\/www.theSQLReport.com\/?p=2040","title":{"rendered":"SQL Server – List of All Databases with Compatibility Levels"},"content":{"rendered":"

Adding this because I was not able to find this query on the first page of my Google search.\u00a0 Wanted a list of all the databases in the SQL Server instance with a readable compatibility level for each database.<\/p>\n

\"\"<\/a><\/p>\n

SELECT\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Server Name] = @@SERVERNAME,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name as 'Database Name',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 compatibility_level as 'Compatibility Level',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [SQL Server Version Compatibility] = CASE sys.databases.compatibility_level\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN '160' THEN 'SQL Server 2022'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN '150' THEN 'SQL Server 2019'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN '140' THEN 'SQL Server 2017'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN '130' THEN 'SQL Server 2016'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN '120' THEN 'SQL Server 2014'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN '110' THEN 'SQL Server 2012'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN '100' THEN 'SQL Server 2008'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN '90' THEN 'SQL Server 2005'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN '80' THEN 'SQL Server 2000'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN '70' THEN 'SQL Server 7.0'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN '60' THEN 'SQL Server 6.0'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE Null\r\nEND\r\nFROM sys.databases\r\nwhere name not in ('master','tempdb','model','msdb')\r\norder by name;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Adding this because I was not able to find this query on the first page of my Google search.\u00a0 Wanted a list of all the databases in the SQL Server … Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":2042,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[195,196],"class_list":["post-2040","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-compatibility-levels","tag-list-databases"],"_links":{"self":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/2040"}],"collection":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2040"}],"version-history":[{"count":7,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/2040\/revisions"}],"predecessor-version":[{"id":2045,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/2040\/revisions\/2045"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/2042"}],"wp:attachment":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2040"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2040"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2040"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}