sp_MSforeachtable

August 7, 2001

Is there such a thing as a task where you would not need a cursor? Hidden in the depths of the Master database are a series of stored procedures that can replace some cursors with these one-liners.

Traditionally if you wanted to run a DBCC CHECKTABLE on every table in a database you'd have to write an elaborate cursor like below :

Beginning with version 6.5 of SQL Server, Microsoft provides a stored procedure called sp_MSreachfortable. Using the question mark as a place holder for all table names, the procedure will do the same as the above query in a single line. You can replace the above cursor with this :

You can issue up to three commands to the stored procedure using @command1 through @command3.