Disadvantages of using sp_MSforeachdb

Question:   We have some scripts which dynamically generate sql code. For example on how to generate code for change all database owners to sa

SELECT 'use '+QUOTENAME(name)+ ' exec sp_changedbowner @loginame =''sa'' ' FROM sys.databases AS d WHERE d.database_id>4 and d.is_read_only = 0

I’d like to streamline the process and investigate some alternatives. One of the alternatives is to utilise sp_MSforeachdb.

What are the some of the disadvantage to using sp_MSforeachdb ?

Answer:  sp_MSforeachdb has many benefits and is a very handy DBA tool . But it doesn’t satisfy all situations.   These are some of the disadvantages

1. If you need more control and granular flexibility then you’ll need to roll your own code. For example if you wanted to exclude system databases or exclude read only databases.Read Simple example of WHILE loop to iterate through sys databases
2. It’s an undocumented command. It is subject to change at every version upgrade
3. As it’s an undocumented and unsupported procedure – how dependable is the procedure with every version upgrade? In my opinion, sp_MSforeachdb should at best should only be used for adhoc DBA type of work , as opposed to using it in active code

Read More

Performance Myth : Replacing a CURSOR with a WHILE LOOP and think you are not processing with a row