Using Dynamic SQL in Cursor Versus sp_MSforeachdb
Recently I wrote an article on MSSQLTips.com regarding executiing UPDATE STATISTICS against all databases on a SQL instance (http://www.mssqltips.com/tip.asp?tip=1606). A reader stated a solution of using the undocumented stored procedure sp_MSforeachdb to accomplish the same thing with less code. Having authored an article earlier in the year on sp_MSforeachdb (http://www.mssqltips.com/tip.asp?tip=1414) I initially thought that was a great idea. Then I tried it out and noticed something interesting.
The code that was posted for sp_MSforeachdb is presented below:
sp_MSforeachdb ‘if ”?” NOT IN (”tempdb”,”master”,”model”,”msdb”) use ? exec sp_updatestats’
The structure of the command is sufficient that it will run without error. What is interesting however, is that the excluded databases (in this case tempdb, master, model, and msdb still are included in the process. Why it does not exclude these or thows an error baffles me. On my test instance this process ran for a total of
As for the code associated with my solution of using a cursor to build a dynamic query that can then be posted into a new query window:
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE [name] NOT IN (‘model’, ‘tempdb’)
ORDER BY [name]
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = ‘USE [‘ + @DB +’]’ + CHAR(13) + ‘EXEC sp_updatestats’ + CHAR(13)
PRINT @SQL
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
In this case the excluded databases stay excluded. So I guess this begs the question, which alternative do you DBAs use? Do you utilize cursors to run dynamic SQL against your databases or do you stick with the sp_MSforeachdb? I use both, but in this instance, what I thought were similar solutions (barring the differences in exluded databases) yeilded different results.
SQLAgentMan,
I may have figured out the problem and a resolution to this issue.
Using a simpler example of:
exec sp_MSforeachdb ‘If ”?” Not In (”tempdb”, ”master”, ”model”, ”msdb”) Use ? Select DB_Name(), ”?” ‘;
with a result of:
master master
master tempdb
master model
master msdb
ReportServer ReportServer
ReportServerTempDB ReportServerTempDB
AdventureWorksLT AdventureWorksLT
AdventureWorks AdventureWorks
AdventureWorksDW AdventureWorksDW
(where each result line was in a separate result set)
I changed the example by adding a set of “Begin … End” statements around the “True” clause of the If statement as follows:
exec sp_MSforeachdb ‘If ”?” Not In (”tempdb”, ”master”, ”model”, ”msdb”) Begin Use ? Select DB_Name(), ”?” End ‘;
with a result of:
ReportServer ReportServer
ReportServerTempDB ReportServerTempDB
AdventureWorksLT AdventureWorksLT
AdventureWorks AdventureWorks
AdventureWorksDW AdventureWorksDW
(where each result line was in a separate result set)
I believe that without the Begin … End sequence, only the Use statement was getting conditionally executed, and the second statement (sp_updatestats) was getting executed every time unconditionally. The Begin … End bracketing makes both statements part of the conditional execution.
I’m all for shorter SQL scripts – sp_MSforeachdb and sp_MSforeachtable help us get there.
________________________
For some uses of sp_MSforeachdb, you can eliminate the need for the Use statement (and possibly the Begin … End sequence) by pointing the query to the desired database in the FROM clause table references. Using your earlier example of:
EXEC sp_MSforeachdb ‘USE ? SELECT name FROM sysobjects WHERE xtype = ”U” ORDER BY name’
I changed it to:
EXEC sp_MSforeachdb ‘SELECT name FROM ?..sysobjects WHERE xtype = ”U” ORDER BY name’
________________________
I have also found that the spelling of sp_MSforeachdb can be critical. I had a situation where I was successfully using a spelling of sp_MSForEachDB on one DB instance (for better “readability”), only to have it fail on another DB instance. I never fully tracked it down, but I suspect it may have to do with case sensitivity or collations that were configured on the two DB instances. The exact spelling is _MSforeachdb – with only the MS capitalized and all the rest in lower case, and should work in all cases (pardon the pun).
Thanks again for your great post! By the way, do you have a theme song to the tune of the “Secret Agent Man” TV show?
Scott R.
At the end of the previous post, I accidently left off the sp_ from the beginning of the stored procedure name. The corrected exact spelling is sp_MSforeachdb.
Sorry for the confusion.
Scott R.
Follow-up: I recently encountered a different issue that can impact the successful use of sp_MSforeachdb. If any of the databases are not in a “normal” state (say – recovering, offline, etc.), the stored procedure will still iterate through them and try to execute the command(s) that were requested. The question is: Will those command(s) run successfully against a database that is not in a “normal” state? Even simple commands like “sp_helpdb ?” may fail on a database in a recovering state (or other not normal state).
I have seen this same issue affect the use of SQL Server Management Studio, although I am not sure if it is directly due to the use of sp_MSforeachdb or other code that tries to do the same thing (return all databases regardless of their status).
I wish that the sp_MSforeachdb stored procedure had an option to just return the databases that are in the normal state, and that option would be the default behavior. It would be OK to allow sp_MSforeachdb to retrieve databases that are in the non-normal state if the option is explicitly overridden by the requestor.
The fact that these stored procedures (sp_MSforeachdb and sp_MSforeachtable) are unsupported is another dilemma that should be resolved by Microsoft. They are useful enough to others that they should be supported and enhanced as appropriate. To my knowledge, they have been around for at least three versions (2000, 2005, 2008) and possibly longer.
Anyone else have thoughts on this issue?
Scott R.