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.
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.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.