February 14, 2013 at 3:17 am
Hi Team,
one challenging requirement for me,
In my Database, i have 100+ tables, triggers, stored proc's, functions,
in that am using cursors,
know i want to list all the objects those objects using cursors.
eg: suppose in a trigger 'Trg_Elim' am using cursor 'CRS'
want to query to list the object names.
February 14, 2013 at 3:25 am
select * from sys.sql_modules where definition like '%declare cursor%'
also why cursors and not set based operations?
February 14, 2013 at 3:41 am
Hi Thank u for your reply,
but the query is not feching all the definitions which are having cursors, only few definication is getting.
how to get all objects / definition.
February 14, 2013 at 3:46 am
Its per database so you will need to run it in every DB.
Also sql_modules stores the definition for
P = SQL Stored Procedure
RF = Replication-filter-procedure
V = View
TR = SQL DML trigger
FN = SQL scalar function
IF = SQL inline table-valued function
TF = SQL table-valued-function
R = Rule (old-style, stand-alone)
But if its not bringing back a object you know has a cursor, check the cursor definition and change the like filter accordingly.
February 14, 2013 at 8:12 am
Minnu (2/14/2013)
Hi Thank u for your reply,but the query is not feching all the definitions which are having cursors, only few definication is getting.
how to get all objects / definition.
Change the select that Anthony posted. It won't find your cursor for you.
select * from sys.sql_modules where definition like '%cursor%'
Cursor definitions are: DECLARE [CursorName] CURSOR
I hope your trying to find them all so you can remove them all.
eg: suppose in a trigger 'Trg_Elim' am using cursor 'CRS'
I physically shuddered when I read this. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 14, 2013 at 8:21 am
Doh. not used cursors in a couple of years, so forgot the syntax.
February 14, 2013 at 8:28 am
anthony.green (2/14/2013)
Doh. not used cursors in a couple of years, so forgot the syntax.
I had to look it up myself. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply