sp_MSforeachdb and sp_MSforeachtable

Sometimes it happened that you need to run a single statement on entire tables / database.  so most of the time we think of cursor which run for each sys.objects (table)  and we execute the dynamic statement by replacing table name.

Although the solution we are thinking is correct there is no issue at all but SQL SERVER made our life more each by providing “sp_MSForEachTable”  which is a hidden stored procedure.

By the name it is clear that it will run on each table.

Lets understand this by an example. Suppose you want to  row count of each table then you can write following statement.

DECLARE @tblRowCount AS TABLE (Counts  INT,
TableName VARCHAR(100))

INSERT INTO @tblRowCount (Counts,TableName)
EXEC sp_MSforeachtable
@command1='SELECT COUNT(1) As counts,"?" as tableName FROM ?'

SELECT * FROM @TblRowCount ORDER BY Counts desc

Now when we run it we will get row count of each table as shown in below snap

sp_msforeachtable_rowcount

sp_MsForeachtable is useful to run command on each table of selected database. Now what if we want to run command on each database also ? then in that case we need to take help of sp_MsForeachDB.

Sp_MsForEachDB  by the name it is clear that it will run provided command to each database.

The syntax of sp_MsForEachDB  is very simple almost like sp_MsForEachTable.

Lets understand this by an example

Suppose we want name of each database  of our SQL server for this we will run following command

Execute sp_MSforeachdb " SELECT  '?' AS Name"

Now  when we execute it we will get following result as shown in below figure.

sp_MSForEachDB