Useful Undocumented sp_MSforeachdb/sp_MSforeachtable

Finding new stuff about SQL Server always excites me. Some SQL professionals may be aware of these two store procedures. However I just knew them a couple of months ago and I absolutely love them!

sp_MSforeachdb is undocumented store proc which presents in master database on SQL Server by default. It allows you to process a single command or multiple commands against every database on the incidence.

Sample 1 Check orphan users on all databases

EXECUTE sp_MSforeachdb @command1 = '?.dbo.sp_change_users_login ''Report''';

This command would return a result set corresponding to each database on the server. It's not user friendly and easy to see if there are a lot of databases. Therefore I will use a temp table to restore all the results, and they could be re-used if it's needed.

Sample 2,  Capture the DATA and LOG files information for all the database on the same SQL incidence.

  IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
       DROP TABLE #Temp;

  CREATE TABLE #Temp
   (
       [Name] VARCHAR (128),
       [FileID] INT ,
       [FileName] VARCHAR (255),
       [FileGroup] VARCHAR (128),
       [Size] VARCHAR (128),
       [Maxsize] VARCHAR (128),
       [Growth] VARCHAR (128),
       [Usage] VARCHAR (128)
 );

EXECUTE sp_MSforeachdb @command1 = 'use [?] INSERT #Temp EXEC sp_helpfile';

SELECT * FROM #Temp


Sample 3, collect SQL Server Database Objects

IF Object_Id('tempdb..#DBObjects') IS NOT NULL
     DROP TABLE #DBObjects;

CREATE TABLE #DBObjects(dbName sysname ,objName VARCHAR (200),objtype CHAR (2) );

EXECUTE sp_msforeachdb @command1 = 'Insert #DBObjects select ''?'' as DBName, name, xtype From ?..sysobjects';

SELECT * FROM #DBObjects

sp_MSforeachtable is also an undocumented store proc, like sp_MSforeachdb. It's used to run a single query or multiple queries against all the tablesin one database. There is the syntax for calling the sp_MSforeachtable Stored procedure:

EXEC @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand

Arguments
@RETURN_VALUE - the return value which will be set by "sp_MSforeachtable"
@command1 - first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)
@replacechar - character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
@command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2
@whereand - can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000)
@precommand - nvarchar(2000) parameter and specifies a command to be run prior to processing any table
@postcommand - nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables

I haven't been crossed any scenarios when sp_MSforeachtable could be used. However I do think they both are very useful store procs and save DBAs time to go through each database or table for the same information.