Microsoft provides a very handy system stored procedure called sp_msForEachTable with SQL Server. This SP is designed to easily allow the database administrator to perform simple tasks on every table in a database with a single command.
While sp_msForEachTable works just fine in most cases, there is one problem with it–it attempts to cater to multiple object owners in a database by using the [owner].[object] syntax. This would be great, except that many commands (including ‘sp_recompile’ and all the ‘DBCC’ commands) cannot deal with the ownership clause; they can only deal with a plain old table name.
Given that many database administrators try to avoid having multiple object owners in a database simply because it’s an administrative headache, I decided to write a version of the standard procedure that does away with the object owner clause, and here it is.
It’s basically a straight copy of the existing SP (all MS copyrights acknowleged) with just one amendment–I have removed the references to the object owner, so it will only work for tables owned by the current user. As objects are most often owned by the DBO, and you are the database administrator, this should not normally be a problem.
I have renamed the script and included a double underscore. The ‘sp_’ allows me to place it in the master database and access it from any other database, and the second underscore lets me easily differentiate my own code from the MS-supplied stuff.
Click here for procedure code.
Usage Restrictions
There’s still plenty of room for improvement. The script as it stands does not handle table names that include a space, and commands that expect an object_id rather than a table name are not catered for–that’s why the DBCC SHOWCONTIG usage example works in SQL 2000 only.
Notes on how the “ForEach” series works
The ‘ForEach’ series of stored procedures includes sp_msForEachTable (discussed above), sp_msForEachDB, which operates at database level rather than table level, and sp_msForEachWorker, which is a utility called by the other two.
When you issue a command using sp_msForEachTable or sp_msForEachDB the stored procedure uses the dynamic SQL technique to replicate your command over each table or object. These commands are stored in a cursor called ‘hcForEach’. Once the cursor has been built, the SP sp_msForEachWorker is called, which fetches and executes commands from the cursor one by one. If you are learning about dyunamic SQL it’s worth taking a look at these SPs to see how things are done.
Further reading
sp__ForEachTable is capable of more complicated processing than the simple examples shown here. As it is basically a hack of the Microsoft SP sp_msForEachTable, you can read about that in Books Online for more information.
This SP has been tested on SQL 7 and SQL 2000.
