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

List out all the procedures which uses a particualr table. Expand / Collapse
Author
Message
Posted Monday, April 27, 2009 5:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:01 AM
Points: 1,557, Visits: 2,613
Dear All,

I have modified the structure of a base table.
Now I want to find out all the procedures using this particular table.
(In some procedures I have written dynamic queries which uses the above table)
Is there any way..? Pls help...

Thanks in advance.
Post #704832
Posted Monday, April 27, 2009 5:45 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:13 AM
Points: 595, Visits: 1,042
Hi San,

Try to build script with sp_helptext 'procedure_name'. It resolves procedure's body to rowset.
You can use cursor which will go through all procedures (which are in sysobjects table) and print only those which consist your table.
Hope it helps,

Cheers,
Bartek
Post #704837
Posted Monday, April 27, 2009 5:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
select object_name(object_id) from sys.sql_modules where definition like '%tablename%' should do it.
You can modify the search condition any other way that suits your needs.


"Keep Trying"
Post #704842
Posted Monday, April 27, 2009 6:01 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:13 AM
Points: 595, Visits: 1,042
I have been spending too much time with SS2000, Chirag advice is definitely faster.
In my solution you can use in SS2005 sys.objects table.
Post #704848
Posted Monday, April 27, 2009 6:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:01 AM
Points: 1,557, Visits: 2,613

Thanks Chirag, this is wot exaclty I wanted.


Cheers.
Post #704878
Posted Wednesday, April 29, 2009 2:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
San (4/27/2009)

Thanks Chirag, this is wot exaclty I wanted.


Cheers.


Welcome


"Keep Trying"
Post #706531
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse