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.
CREATE PROCEDURE dbo.sp_foreachroutine
@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
/* 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)
/* 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 '
+ ' ) '
+ COALESCE(@whereand, '')
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)
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'