Following my previous post on mass change of PAGE_VERIFY option, I want to share one other script that you can use if you are using a Centralized Management Server and multi-server queries in order to change the compatibility level of multiple databases and instances.
With the below script you can change the compatibility level of all databases and in my example I am setting it to compatibility_level = 100
--mass change the compatibility level option for all instances and --databases that are not read only and have current comp. set to 80 or 90 EXEC sp_MSforeachdb ' use [?] if (select count(*) from sys.databases where compatibility_level <100 and name=''?'' and database_id>4 and is_read_only = 0 )=1 begin alter database [?] set compatibility_level = 100 PRINT ''compatibility changed to 100 for database = '' + DB_NAME() end'