SQL Server How do I find all databases which are not running at the server compatibility level they could?

hgb9j2n6 于 2023-03-07 发布在其他

关注(0)|答案(1)|浏览(166)

Some databases might not have their compatibility levels set to the most recent version of SQL Server which is installed. I want to automate that detection without ever typing in the current compatibility level, how can I identify them through T-SQL?

sql-server

来源：https://stackoverflow.com/questions/75606909/how-do-i-find-all-databases-which-are-not-running-at-the-server-compatibility-le

1条答案

按热度按时间

bq3bfh9z1#

SERVERPROPERTY('ProductVersionMajor') returns the version number i.e. 14.

The compatibility_level in sys.databases for a new database in version 14 will be 140.

Hence here I multiply by 10

SELECT name, compatibility_level FROM sys.databases 
where compatibility_level  < (CAST(SERVERPROPERTY('ProductMajorVersion') as int)*10)

However, application vendors may not have tested their code with this compatibility level, so it is not always safe to assume you can alter every database to match the current server level.

This doesn't work for azure databases because Microsoft Went totally loopy with their version numbering. see this article

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server How do I find all databases which are not running at the server compatibility level they could?

1条答案

相关问题

热门标签

最新问答