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'
sp_MSforeachdb 'USE [?]; EXEC sp_helpfile'
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 Twitter | Follow me on FaceBook
