I really like sp_msforeachdb. For example, here is the sql to my regular maintenance jobs:
- exec sp_MSforeachdb ‘DBCC CHECKDB(?) with NO_INFOMSGS, ALL_ERRORMSGS’
- exec sp_msforeachdb ‘if db_id(”?”) <> db_id(”master”) and db_id(”?”) <> db_id(”tempdb”) begin use [?];exec sp_dbreindex_or_indexdefrag;end’
- exec sp_msforeachdb ‘if db_id(”?”) <> db_id(”master”) and db_id(”?”) <> db_id(”tempdb”) begin use [?];exec sp_updatestats;end’
Yesterday I was reducing the number of vlfs and to check I’d covered all databases I ran:
- sp_msforeachdb ‘use ?;select db_name();dbcc loginfo’
See what I mean?
The only hitch is if your database name has a reserved character – here there are some with a dash – you’ll need to do [?] instead of just ?.