How to Exclude Read_Only Databases Using sp_MSforeachdb
March 28, 2017
I will explain how you can use sp_MSforeachdb stored procedure against READ_WRITE database only.
There are numerous articles on the web about excluding databases using database id or name. Recently I needed a script to run against databases that are part of Always On Availability Groups and in READ_WRITE status. I used the ‘Updateability’ properties of DATABASEPROPERTYEX function.
Here is how I implemented this.
SET NOCOUNT ON EXEC sp_MSForEachDb ' USE [?] IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''Distribution'') IF ((SELECT DATABASEPROPERTYEX(''?'', ''Updateability''))=''read_write'' ) BEGIN ..... ..... END '
I would like to know if you implemented the same functionality using other techniques