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

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *