Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Creating your own sp_MSforeach stored procedure

By Brian Knight,

Making your own SP_MSFOREACH stored procedures

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:

 

Parameter

Description

@command1

First command to execute against every object

@replacechar

What placeholder will be used to represent an object. By default, this is ‘?’.

@command2

Second command to execute against every object

@command3

Third command to execute against every object

@whereand

WHERE clause applied to narrow down the list of objects.

@precommand

Command run a single time before the loop.

@postcommand

Command run a single time after the loop.

 

 

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:

 

 

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!

 

Total article views: 14886 | Views in the last 30 days: 9
 
Related Articles
FORUM

TSQL Error using Cursor to loop through sysdatabases (Syntax Error)

Incorrect syntax near @dataname

ARTICLE

SP_MSForeachtable - Life Without Cursors.

Is there such a thing as a task where you would not need a cursor? Hidden in the depths of the maste...

FORUM

Cursor

cursor

FORUM

How to Group and batch select/cursor results in stored procedure

How to Groupand batch select/cursor results for e-mail

FORUM

select multirecs from single recs without cursor

select multirecs from single recs without cursor

Tags
administration    
advanced querying    
sql server 7    
stored procedures    
triggers    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones