Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I run a query against all my databases or all my tables?

Undocumented Procedures

How do I run a query against all my databases or all my tables?

by  SQLBill  Posted    (Edited  )
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

Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top