The hidden Stored Procedures in SQL

Not all SQL stored procedures have been documented. Some of these hidden/undocumented SP are quite useful. We’ll begin this series of blog by discussing some of these, one at a time.

Today we’ll discuss about the hidden SP - sp_MSforeachtable.

This very versatile SP works on all the tables in your databases. As such it can be used to run same query/code on multiple tables in a database. It is present in the master database.

It also supports one or more commands as arguments and we can use wild card character ? in the command queries  to take care of different tables.

SYNTAX

EXEC @RETURN_VALUE=sp_MSforeachtable @command1,

     @replacechar,

     @command2,

     @command3,

     @whereand,

     @precommand,

     @postcommand

where:

@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

Some example usages are below-

·                                                         ·         Run DBCC check on all tables.

EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?])'

·         Count rows in each table

CREATE TABLE #counts ( TABLE_NAME varchar(255), ROW_COUNT int) 

EXEC sp_MSForEachTable 

@command1=

'INSERT #counts(table_name,row_count) SELECT ''?'',COUNT(*) FROM ?'

SELECT TABLE_NAME,ROW_COUNT

FROM #counts

ORDER BY TABLE_NAME,ROW_COUNT DESC

·         Disable triggers in all tables

EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'