Creating your own sp_MSforeach stored procedure

In SQL Server 6.x, Microsoft introduced SP_MSFOREACHTABLE and SP_MSFOREACHDB. These powerful stored procedures allow a DBA to loop through each table or database and perform an action on the object. Although these are powerful stored procedures, it is disappointing that they have not been extended into other objects. This article will show you how to fully extend this stored procedure to work in other areas of SQL Server.

First let’s talk briefly about how these stored procedures work today and then I’ll get into how to make them do what we want them to do to other objects. The two stored replace places where you would have to write a complex cursor as shown below with a single line of code:

--Old way

DECLARE @dataname varchar(255),
@dataname_header varchar(255)

DECLARE datanames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
WHERE name not in ('master', 'pubs', 'tempdb', 'model')
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
IF (@@fetch_status = 0)
BEGIN
SELECT @dataname_header = "Database " + RTRIM(UPPER(@dataname))
PRINT @dataname_header
SELECT @dataname_header = RTRIM(UPPER(@dataname))
EXEC ("DBCC CHECKDB " + "(" + @dataname + ")")
END
CLOSE datanames_cursor
DEALLOCATE datanames_cursor

The new way to do replace the above syntax is to run the following line of code:

--New way

sp_MSforeachtable @command1="print '?' dbcc checktable ('?')"

The above syntax will run DBCC CHECKTABLE, which performs consistency checks against every table. Although behind the scenes, a cursor is still being run, at least you won’t have to worry about it or its syntax. To execute the stored procedures as they are now, you must only pass in a single parameter, @command1.  So if you wished to loop through every table and count the number of records, you could use the following syntax:

sp_msforeachtable "Print '?' select count(*) from ?"

As you can see, the question mark (?) represents the table name in this case. Here are some more advanced parameters that can be used:

So, to fully use some of the above syntax, here’s an example that will capture all tables that begin with the word categories:

sp_MSforeachtable @command1 = "Print '?'",

                          @command2 = "select count(*) from ?",

                          @precommand = "Print 'Listing of all tables counts at '  select getdate()",

                          @postcommand = "Print 'Complete!'",

                          @whereand = "and name like 'categories%' order by name desc"

Would output the following results:

Listing of all tables counts at                                                       

------------------------------------------------------

2002-06-14 12:21:54.193

[dbo].[Categories2]           

-----------
0

[dbo].[Categories]           

-----------

92

Complete!

Now that we have the basics out of the way, let’s discuss how to make your own SP_MSFOREACH stored procedure. Each of the SP_MSFORECH stored procedures reference another sproc called SP_MSFOREACHWORKER, which generically accepts an array of data and loops through it. Creating a customized stored procedure can be easily created by modifying a piece of the query. Here is the part of a SP_MS_FOREACH query that you would care about.

            /* Create the select */

   exec(N'declare hCForEach cursor global for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' from dbo.sysobjects o '

         + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '

         + @whereand)

            declare @retval int

            select @retval = @@error

            if (@retval = 0)

                        exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

To make a SP_MSFOREACHVIEW stored procedure, all you would have to do is change the bolded N''IsUserTable'' to N''IsView''  or for a trigger you could use the IsTrigger syntax. The final cut of code would look like the below:

   exec(N'declare hCForEach cursor global for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' from dbo.sysobjects o '

         + N' where OBJECTPROPERTY(o.id, N''IsView'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '

         + @whereand)

            declare @retval int

            select @retval = @@error

            if (@retval = 0)

                        exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

Here are the links to the full script in the SQLServerCentral.com script library to get you started so you won’t have to be bothered to create some of the basics:

• SP_MSFOREACHVIEW – Views
• SP_MSFOREACHPROC – Stored Procedures
• SP_MSFOREACHTRIGGER – Triggers
• SP_MSFOREACHFK – Foreign Keys

The chunk of code that I included above can be modified further to perform additional actions. If you come up with a neat way to extend this sproc, please post the in the script library and share them with the community!