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