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.