Disadvantages of using sp_MSforeachdb

15 November,2016 by Jack Vamvas

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

 

 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Disadvantages of using sp_MSforeachdb


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer