T-SQL: sp_MSForEachDB and sp_MSForEachTable – Undocumented but very powerful stored procedures

I recently came across the following two undocumented stored procedures:

• sp_MSforeachdb
• sp_MSforeachtable

These stored procedures take a command (infact upto 3 commands) and run it for all the objects (databases on a server or tables inside a database, depending upon the stored procedure). Here are a few examples of the usage of these stored procedures (Note that these statements may take long duration depending upon the number of databases/tables):

--Display no. of objects per databases
exec sp_MSforeachdb 'select "?", count(*) as TotalObjects from [?].dbo.sysobjects'

--Display no. of rows per table
exec sp_MSforeachtable 'select "?" as TableName, count(*) as TotalRows from ?'

--Display total space used for each table
exec sp_MSforeachtable 'exec sp_spaceused [?]'

Beware, never ever underestimate the powers of these stored procedures. In a single line, you can:

• Destroy all the databases on a server
• Delete all the data inside all the tables
• Drop all the tables
• Even more dangerous things I can’t think at this time

In this small post, I am not going to demonstrate how to do the above mentioned tricks but make sure you are extremely cautious when using the above stored procedures.