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
