INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Undocumented Procedures

How do I run a query against all my databases or all my tables? by SQLBill
Posted: 3 Sep 03

Of the many undocumented stored procedures, I find that I am refering people to these two quite often. They allow you to run up to three commands against every database or every table in a database.

First I need to give credit where it is due. I found out about these in The Guru's Guide to Transact-SQL by Ken Henderson.

The two stored procedures are sp_MSforeachdb and sp_MSforeachtable.  Note: the MS does not have to be capitalized. Except for the name and that one is for every database and one is for every table, they are the same. So I am only posting the one for sp_MSforeachtable. To use it to run commands against all databases, just change sp_MSforeachtable to sp_MSforeachdb.

------------------------------------------------
Procedure:
Sp_MSforeachtable
@command1
@replacechar = '?'
[,@command2]
[,@command3]
[,@whereand]
[,@precommand]
[,@postcommand]

Purpose:
Execute up to three commands for every table in a database (optionally matching the @whereand clause) @replacechar will be replaced with the name of each table. @precommand and @postcommand can be used to direct commands to a single result set.

Example:
EXEC sp_MSforeachtable @command1 = 'PRINT "Listing ?=', @command2='SELECT * FROM ?', @whereand=' AND name like "title%"'
--------------------------------------------------------

Again, I would like to thank Ken Henderson for 'documenting' this Microsoft undocumented stored procedure.

-SQLBill


Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close