SQL Server: sp_msforeachdb – undocumented stored procedure

Dear all,

In my last blog, I talked about sp_msforeachtable. You can find it here;

In this post, I want to talk about sp_msforeachdb. Some member here already posted comments on my previous blog. Thanks to them.

sp_msforeachdb allows to iterate through all the databases in you instance. Here are some examples:

sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

1_SQL_Server_sp_msforeachdb_undocumented_stored_procedure

   
sp_MSforeachdb 'USE [?]; EXEC sp_helpfile'

2_SQL_Server_sp_msforeachdb_undocumented_stored_procedure

Another interesting use could be to change all the DB owners to sa.

EXEC sp_MSforeachdb
@command1='use ?; exec sp_changedbowner ''sa'''

Now , all this being said, please note that this stored proc is undocumented and unsupported. It might be discontinued in the future. I also read on internet that some people found bugs in this sp where it missed some databases while iterating through them. Don’t know why. You can certainly use it but I don’t recommend its usage for critical tasks.

 

Regards

Rahul Sharma

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

Leave a Reply

Your email address will not be published.