SQL Iterators: Making the Life of a SQL Server DBA That Much Easier

EXECUTE sp_MSForEachTable
N'UPDATE STATISTICS ? WITH FULLSCAN,ALL';

[sys].[sp_MSforeachTable]

@command1 NVARCHAR (2000)

, @replacechar NCHAR (1)=N'?'

, @command2 NVARCHAR (2000)=NULL

, @command3 NVARCHAR (2000)=NULL

, @precommand NVARCHAR (2000)=NULL

, @postcommand NVARCHAR (2000)=NULL
[sys].[sp_MSforeachdb]

@command1 NVARCHAR (2000)

, @replacechar NCHAR (1)=N'?'

, @command2 NVARCHAR (2000)=NULL

, @command3 NVARCHAR (2000)=NULL

, @precommand NVARCHAR (2000)=NULL

, @postcommand NVARCHAR (2000)=NULL


@replacechar is the replacement character in your command that will be replaced by the table name as the procedure iterates over the list of all tables.
@command2 and @command3 – since this procedure has been around A LONG time there were two purposes for these commands. In earlier versions of SQL Server, there were limits to the length of characters strings. So, @command2 and @command3 could be separate commands or if they began with’ +++’ then their text would be appended to the end of @command1.
@precommand and @postcommand were the opportunity to execute singleton commands prior to and after iterating though and processing @command1 for all the tables.

SQLIterators assembly implements both of these stored procedures with slightly different interfaces. In both cases, since we can now support large character strings I do not support the ‘+++’ convention to concatenate @command1 with @command2 and @command3. Also, due to the way that ADO.NET server side context connections work, I don’t have a database context available for the sp_MSForEachTable replacement, so that must be provided as a parameter.
So that leaves us with the two following stored procedures:

[dbo].[ExecuteForEachDB]

@command1 NVARCHAR (4000)=N''               --required parameter

, @replacechar NVARCHAR (1)=N'?'            --optional parameter

, @command2 NVARCHAR (4000)=N''             --optional parameter

, @command3 NVARCHAR (4000)=N''             --optional parameter

, @precommand NVARCHAR (4000)=N''           --optional parameter

, @postcommand NVARCHAR (4000)=N''          --optional parameter

, @setnocounton BIT = true                  --optional parameter

, @messageOptions NVARCHAR (8) =N'Errora'   --optional parameter

WITH EXECUTE AS CALLER

AS EXTERNAL NAME [SqlIterators].[SqlIterators.MSForEachReplacements].[ExecuteForEachDB]

GOCREATE PROCEDURE [dbo].[ExecuteForEachTable]

@command1 NVARCHAR (4000)=N''                --required parameter

, @database NVARCHAR (128)=N''               --required parameter

, @replacechar NVARCHAR (1)=N'?'             --optional parameter

, @command2 NVARCHAR (4000)=N''              --optional parameter

, @command3 NVARCHAR (4000)=N''              --optional parameter

, @precommand NVARCHAR (4000)=N''            --optional parameter

, @postcommand NVARCHAR (4000)=N''           --optional parameter

, @setnocounton BIT = true                   --optional parameter

, @messageOptions NVARCHAR (8) =N'Errora'    --optional parameter

WITH EXECUTE AS CALLER

AS EXTERNAL NAME [SqlIterators].[SqlIterators.MSForEachReplacements].[ExecuteForEachTable]



EXECUTE master.dbo.ExecuteForEachDB

'SELECT ?, OBJECT_NAME(object_id) FROM sys.dm_db_partition_stats ; SELECT ?'

, @messageOptions = 'Progress';



exec master.dbo.ExecuteForEachTable

N'UPDATE STATISTICS ? WITH FULLSCAN,ALL'

, @database = N'AdventureWorks'

, @messageOptions = 'Errors'

Valid choices for the @messageOptions parameter are:



Option:


Errors
Only return errors


Progress
Errors, and progress messages


All
Verbose – debugging messages


Invalid
If the user passed an invalid option




So go to SQLIterators , grab the binaries and experiment. The CodePlex site will also host any user submitted scripts that DBAs care to share with the community. To be clear, please note that the assembly, full source code and documentation for this tool are available on www.CodePlex.com here: SQLIterators.
That’s all for now. Watch for my next post where we will discuss the SQL Diag Configuration Tool – a utility that makes it easy to setup the SQLDiag configuration file, without having to edit an XML file.
Until the next time,
 this is boB ‘The Tool Man’ Taylor


Example usage: