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