Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cursor Names - List Expand / Collapse
Author
Message
Posted Thursday, February 14, 2013 3:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:59 AM
Points: 227, Visits: 709
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
Posted Thursday, February 14, 2013 3:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 5,143, Visits: 4,948
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
Posted Thursday, February 14, 2013 3:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:59 AM
Points: 227, Visits: 709
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
Posted Thursday, February 14, 2013 3:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 5,143, Visits: 4,948
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.




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 #1419932
Posted Thursday, February 14, 2013 8:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
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.


_______________________________________________________________

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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1420101
Posted Thursday, February 14, 2013 8:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 5,143, Visits: 4,948
Doh. not used cursors in a couple of years, so forgot the syntax.



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 #1420112
Posted Thursday, February 14, 2013 8:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1420115
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse