Introduction
sp_msforeachdb and sp_msforeachtable are very powerful stored procedures. They allow you to loop through the databases and tables in your instance and run commands against them. I have used these extensively in my day to day work as a DBA. Both of the stored procedures use a question mark as a variable subsitution character. When using sp_msforeachdb, the "?" returns the databasename, and when using sp_msforeachtable the "?" returns the tablename.
Using the Stored Procedures
sp_msforeachdb
Example #1 - to do a check db on every database in your instance you could issue the following command:
sp_msforeachdb 'dbcc checkdb( ''?'' )'
Example #2 - to change the owner of each database in the instance to sa.
sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
print ''?''
exec [?].dbo.sp_changedbowner ''sa''
END'
**Notice how I used an if statement to filter out the system databases
Example #3 - to do a check db on every table in the database you could issue the following command:
sp_msforeachdb 'dbcc checktable( ''?'' )'
Example #4 - to shrink every database on the instance. Be careful with this one. Not something you want to run on a production server during business hours.
sp_msforeachdb 'dbcc ShrinkDatabase( ?, 10 )'
Example #5 - to make a user db_owner on each user database in the instance. This is commonly done for apps like SharePoint that require db_owner in order to apply service packs.
sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
print ''?''
exec [?].dbo.sp_adduser ''<YOUR DOMAIN NAME HERE>\<YOUR USER ACCOUNT HERE>''
exec [?].dbo.sp_addrolemember ''db_owner'',''<YOUR DOMAIN NAME HERE>\
<YOUR USER ACCOUNT HERE''
END'
sp_msforeachtable
The counterpart to sp_msforeachdb. Once again, the procedure uses the "?" character to signify the name of the table that the command is currently being executed on.
Example #1 - to get a list of each index and when the statistics were last updated on each index.
CREATE table #stats(
table_name nvarchar(255) null,
index_name nvarchar(255) null,
statistics_update_date datetime null
)
GO
exec sp_msforeachtable
'insert into #stats
SELECT
''?'',
name AS index_name,
STATS_DATE(object_id, index_id) AS statistics_update_date
FROM
sys.indexes
WHERE
object_id = OBJECT_ID(''?'');'
select * from #stats where index_name is not null
drop table #stats
There are a million different uses for these stored procedures. The possibilities are endless. You can even nest a sp_msforeachtable inside of a sp_msforeachdb! Have fun and use them with caution!
