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:
[sql]EXEC sp_MSforeachtable ‘DBCC DBREINDEX(”?”)’
[/sql]
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:
[sql]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
[/sql]
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:
[sql]EXEC sp_foreachroutine ‘print ”?”’
[/sql]
For just functions, use the optional @routinetype parameter:
[sql]EXEC sp_foreachroutine ‘print ”?”’, @routinetype = ‘function’
[/sql]
Enjoy!
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;
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…
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.
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
Adam, as ever, a great article !!
Roman
http://sqldata.blogspot.com