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.

Advertisements

One Response to “T-SQL: sp_MSForEachDB and sp_MSForEachTable – Undocumented but very powerful stored procedures”

  1. Tanveer Badar Says:

    Your warnings are reason enough to keep those SPs undocumented.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: