THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Looping over routines using sp_foreachroutine

Of all of the undocumented stored procedures shipped with SQL Server, there are two in particular that I constantly use: sp_MSforeachtable and sp_MSforeachdb. These procedures internally loop over each non-Microsoft shipped (i.e. user-defined) table in the current database, or each database on the current server, respectively. During this loop, the procedures perform whatever action(s) are specified by the user (in the parameters). For instance, what if you want to re-index every table in the database? Sure, you could write your own cursor, but why bother? Use the following T-SQL instead:

 

EXEC sp_MSforeachtable 'DBCC DBREINDEX(''?'')'

Convenient, isn't it? But I won't get into any more detail on these. Gregory Larsen does a good job of that in the article linked above.

What I'd like to show instead is a very simple modification I've made to sp_MSforeachtable. It's great to loop over tables and databases, but sometimes we want to loop over routines (a collective term for procedures, functions, triggers, and views) instead. Perhaps you want to grant pemissions to a user. Or perhaps you want to roll out some TSQLMacro updates to every routine in the database instead of just one, as is supported by the current version of the framework... And now you know how it will be done in the next version.

Presenting sp_foreachroutine:

 

CREATE PROCEDURE dbo.sp_foreachroutine
@command1 nvarchar(2000),
@replacechar nchar(1) = N'?',
@command2 nvarchar(2000) = null,
@command3 nvarchar(2000) = null,
@whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null,
@postcommand nvarchar(2000) = null,
@routinetype nvarchar(20) = null
AS
BEGIN
/* This proc returns one or more rows for each procedure (optionally, matching @where),
with each procedure defaulting to its own result set */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */

/* Preprocessor won't replace within quotes so have to use str(). */
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))

if (@precommand is not null)
exec(@precommand)

/* Create the select */

declare @sql nvarchar(4000)
set @sql =
N'declare hCForEach cursor global for '
+ N' select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + '
+ N' ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' '
+ N' from dbo.sysobjects o '
+ N' where OBJECTPROPERTY(o.id, N''IsMSShipped'') = 0 '
+ CASE @routinetype
WHEN 'procedure' THEN ' and OBJECTPROPERTY(o.id, N''IsProcedure'') = 1 '
WHEN 'function' THEN ' and (OBJECTPROPERTY(o.id, N''IsScalarFunction'') = 1 '
+ ' or OBJECTPROPERTY(o.id, N''IsTableFunction'') = 1) '
WHEN 'view' THEN ' and OBJECTPROPERTY(o.id, N''IsView'') = 1 '
WHEN 'trigger' THEN ' and OBJECTPROPERTY(o.id, N''IsTrigger'') = 1 '
ELSE ' and ( '
+ ' OBJECTPROPERTY(o.id, N''IsProcedure'') = 1 '
+ ' or OBJECTPROPERTY(o.id, N''IsScalarFunction'') = 1 '
+ ' or OBJECTPROPERTY(o.id, N''IsTableFunction'') = 1 '
+ ' or OBJECTPROPERTY(o.id, N''IsView'') = 1 '
+ ' or OBJECTPROPERTY(o.id, N''IsTrigger'') = 1 '
+ ' ) '
END
+ COALESCE(@whereand, '')

exec(@sql)
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

if (@retval = 0 and @postcommand is not null)
exec(@postcommand)

return @retval
END
GO

Regular readers of this blog will note that the formatting isn't consistent with my usual standards. But since this was a port from an MS-written proc, I decided to keep things fairly consistent with what was already there. I've also added an additional parameter that wasn't present in sp_MSforeachtable: @routinetype, which lets the user select a specific type of routine to loop over. So, for instance, if you only want views, pass in 'view'. Same for functions ('function'), triggers ('trigger') and procedures ('procedure'). Pass in any other value -- or leave it NULL -- and you'll get all routines in the database.

This procedure keeps the sp_ prefix on purpose; it's meant to be created in the master database, and makes use of the MS-shipped sp_MSforeach_worker stored procedure, which lets it do its work.

Using it is simple. ? is the default substitution character (this can be changed using the @replacechar parameter). So to print a list of all routines in the current database, use:

 

EXEC sp_foreachroutine 'print ''?'''

For just functions, use the optional @routinetype parameter:

 

EXEC sp_foreachroutine 'print ''?''', @routinetype = 'function'

Enjoy!


Published Wednesday, July 12, 2006 10:45 PM by Adam Machanic
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Gojdas said:

I have also modified the sp_MSforeach<db/table> procs a bit to be a bit safer to execute.  I felt that the global cursor used in each is a pretty dangerous way to go for these stored procs among other things that I fixed.  So I gave it the capability to create and use a local cursor dynamically.  Thus I won't see issues that will occur with the global cursor when/if the proc is run concurrently by same/different users.  It uses a global cursor most likely because it needs to create the cursor dynamically and that is the easiest way to achieve that.  It took a lot of trial and error for me to figure how to create a dynamic local cursor so I thought I might show the code of how to do that.

So here is a code snippet of how to do this:

  DECLARE @SQL            nvarchar(max);

     SET @SQL =

        N'SET @my_cur = CURSOR FAST_FORWARD FOR '

        + N'SELECT ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' '

        + N'  FROM dbo.sysobjects o '

        + N'  INNER JOIN sys.all_objects syso on o.id = syso.object_id '

        + N' WHERE OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 '

        + N' AND o.category & ' + @mscat + N' = 0 ';

  IF @whereand IS NOT NULL BEGIN  

     SET @SQL = @SQL + @whereand;

  END;

  SET @SQL = @SQL + N'; OPEN @my_cur;';

  DECLARE @local_cursor cursor

  EXEC sp_executesql

       @SQL

      ,N'@my_cur cursor OUTPUT'

      ,@my_cur = @local_cursor OUTPUT;

FETCH @local_cursor INTO @name;

WHILE (@@fetch_status >= 0) BEGIN

--<whatever other code>

FETCH @local_cursor INTO @name;

END /* WHILE FETCH_SUCCESS */

  SET @curStatus = Cursor_Status('variable', '@local_cursor');

  IF @curStatus >= 0 BEGIN

     CLOSE @local_cursor;

     DEALLOCATE @local_cursor;

  END;

July 6, 2009 4:54 PM
 

Adam Gojdas said:

Upon looking over the code I see I never explicitly set the LOCAL option.  So maybe I just fooled myself into thinking it worked?  I will need to check the DB setting to see what it defaults to, local or global, and test what occurs when I specifically set it LOCAL.

hope it works...

July 6, 2009 10:42 PM
 

Adam Gojdas said:

Made the change in the code to have explicitly set LOCAL:

        N'SET @my_cur = CURSOR LOCAL FAST_FORWARD FOR '

Tested and it works.  So luckily the DB I had run this on defaulted to a local cursor when not explicitly setting this.  So as I mentioned earlier this way I think would be a safer implementation than using GLOBAL cursors.

July 6, 2009 10:54 PM
 

sharif said:

2 small problems, not sure if i am running the wrong version of SQL server.

the sp_MSforeach_worker seem to refer to hCForEachDatabase (default) or hCForEachTable. so had to change the query to refer to the cursor as hCForEachDatabase.

also got error "cursor operation because the set options have changed since the..." had to add set options as:

... N' set ansi_nulls on ; set quoted_identifier off; declare hCForEachDatabase cursor global for...

thanks for a very handy query

November 20, 2010 9:47 PM
 

Roman said:

Adam, as ever, a great article !!

Roman

http://sqldata.blogspot.com

April 13, 2011 3:05 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement