SQL Server - sp_msforeachdb and sp_msforeachtable

sp_msforeachdb is a hidden stored procedure which is very handy when you want to run a sql command across all of the databases in your instance. The procedure uses the "?" character to signify the name of the database that the command is currently being executed on.

Example #1 - to do a check db on every database in your instance you could issue the following command:

   1:  sp_msforeachdb 'dbcc checkdb( ''?'' )'

at runtime the question mark will be replaced by the database name.

Example #2 - to change the owner of each database in the instance to sa.

   1:  sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN 
   2:     print ''?'' exec [?].dbo.sp_changedbowner ''sa'' 
   3:  END'

Example #3 - to do a check db on every table in the database you could issue the following command:

   1:  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.

   1:  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.

   1:  sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN 
   2:     print ''?'' 
   3:     exec [?].dbo.sp_adduser ''MYDOMAIN\SharepointUser'' 
   4:     exec [?].dbo.sp_addrolemember ''db_owner'',MYDOMAIN\SharepointUser'' 
   5:  END'


sp_msforeachtable is 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.


   1:  CREATE table #stats( 
   2:     table_name nvarchar(255) null, 
   3:     index_name nvarchar(255) null, 
   4:     statistics_update_date datetime null 
   5:  ) 
   6:   
   7:  go 
   8:   
   9:  exec sp_msforeachtable 
  10:     'insert into #stats 
  11:      SELECT 
  12:          ''?'', 
  13:          name AS index_name, 
  14:          STATS_DATE(object_id, index_id) AS statistics_update_date 
  15:      FROM 
  16:          sys.indexes 
  17:      WHERE 
  18:          object_id = OBJECT_ID(''?'');' 
  19:   
  20:  select 
  21:     * 
  22:  from 
  23:     #stats 
  24:  where 
  25:     index_name is not null 
  26:   
  27:  drop table #stats