SQL Iterators: Making the Life of a SQL Server DBA That Much Easier
Many Microsoft SQL Server DBAs automate as many of their day to day processes as possible, freeing them to attend to higher impact activities. To do so, many DBAs leveraged two undocumented system stored procedures, namely sp_MSForEachDatabase and sp_MSForEachTable. The upside of using these stored procedures are tremendous. For example, in a database with 1,500 tables, creating a script to update the statistics for all 1,500 tables is time consuming and must be modified each time a table is added to the database. Enter sp_MSForEachTable. By using this undocumented system stored procedure you could create a script that looked something like this, which automatically performs the task with the current set of tables.
EXECUTE sp_MSForEachTable
N'UPDATE STATISTICS ? WITH FULLSCAN,ALL';
This is a great savings, however there is a downside that we all know. Those SQL Server system objects that are undocumented are also unsupported for user code. Hence, any scripts built upon these stored procedures could be deprecated at any time.
Knowing that DBAs want to automate these types of tasks, I decided to leverage the power of SQL CLR stored procedures to implement a custom assembly that provides equivalent functionality.
So, let’s examine the interface of these two stored procedures and then examine the SQLCLR equivalents.
[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:
@command1 is the command you would like to execute for each table.