Cursor Names

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Cursor Names - List

Cursor Names - List

Rate Topic

Display Mode

Topic Options

Author

Message

Minnu

Posted Thursday, February 14, 2013 3:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 119, Visits: 362

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.

Post #1419917

anthony.green

Posted Thursday, February 14, 2013 3:25 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

select * from sys.sql_modules where definition like '%declare cursor%'

also why cursors and not set based operations?

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1419923

Minnu

Posted Thursday, February 14, 2013 3:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 119, Visits: 362

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.

Post #1419929

anthony.green

Posted Thursday, February 14, 2013 3:46 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1419932

Sean Lange

Posted Thursday, February 14, 2013 8:12 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 8,605, Visits: 8,246

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1420101