Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 13, 2001

"For Each Table" Stored Procedure

By Neil Boyle

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM