Recently I needed to check the compatibility level of a database and SSMS didn’t work. This is what I did in T-SQL.https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16
Querying the System
I would have assumed I could use the DatabaseProperyEX() function to get this, but as I look through the list of properties, the compat level isn’t in there.
When I look through the Docs for the compat level, I find that the page for database compatibility, the page mentions that querying a DMV is the way to check this. Seems strange, but I guess that’s what you do.
Here’s the query:
SELECT name, compatibility_level FROM sys.databases;
Y0u can filter this by database name if you need it.
I needed this as I was testing SQL Server 2022, but my SSMS version (18.10), didn’t recognize level 160. I assumed that was what should have been there, but I needed this query to verify.
Now, hopefully I’ll remember that I just need to query the DMV.
SQL New Blogger
This was a quick post, really just over 5 minutes to write. It’s not super technical, but it does show that I can research something and solve a problem. And I have an alternative when my main tool doesn’t work.
Good skills to showcase on a blog.