A hidden feature sp_MSforeachdb TIP #52

 

As we discussed yesterday a hidden feature tip #51 which is sp_MSForeachtable . sp_MsForeachtable is useful to run command on each table of selected database. Now what if we want to run command on each database also ? then in that case we need to take help of sp_MsForeachDB.

Sp_MsForEachDB  by the name it is clear that it will run provided command to each database.

The syntax of sp_MsForEachDB  is very simple almost like sp_MsForEachTable.

Lets understand this by an example

Suppose we want name of each database  of our SQL server for this we will run following command

Execute sp_MSforeachdb " SELECT  ‘?’ AS Name"

Now  when we execute it we will get following result as shown in below figure.

sp_MSForEachDB

This two stored procedures are not documented but it help a lot in maintenance task and other tasks.

I will share some maintenance query  with sp_MsForEachDB & sp_MsForEachtable  soon.

I hope this will help you somewhere.

Enjoy !!!

RJ!!!

Advertisements

One thought on “A hidden feature sp_MSforeachdb TIP #52

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