Looping over routines using sp_foreachroutine

re: Looping over routines using sp_foreachroutine

Adam Gojdas — Mon, 06 Jul 2009 20:54:12 GMT

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;

re: Looping over routines using sp_foreachroutine

Adam Gojdas — Tue, 07 Jul 2009 02:42:51 GMT

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...

re: Looping over routines using sp_foreachroutine

Adam Gojdas — Tue, 07 Jul 2009 02:54:57 GMT

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.

re: Looping over routines using sp_foreachroutine

sharif — Sun, 21 Nov 2010 02:47:08 GMT

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

re: Looping over routines using sp_foreachroutine

Roman — Wed, 13 Apr 2011 19:05:24 GMT

Adam, as ever, a great article !!

Roman

http://sqldata.blogspot.com