SQL Server Hidden Stored Procedures

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!