adam machanic now blogs at http://sqlblog.com

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!