Monday, October 6, 2014

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.


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 !!!


No comments:

Post a Comment