How to Find SP's Consumed in Whole App

Self-Innovator
Posted by Self-Innovator under Sql Server category on | Points: 40 | Views : 1207
Hi,
This is a Small Code snippet which might be useful in situations where you r running a huge applications there u need to find out which tables are used by which sp's FrontEnd.

Ex:1
If u wanted to Find it out How Many places this table is being used by Sp's (tbIHLCEMain)

SELECT DISTINCT OBJECT_NAME(ID) FROM syscomments where text like '%tbIHLCEMain%'

Will Return the list Of SP's Is being uses the table tbIHLCeMain
Ex:2
To Find out How Many Sp's are Using Insert Statement for table(tbIHLCEMain)
SELECT DISTINCT OBJECT_NAME(ID) FROM syscomments where text like '%INSERT INTO dbo.tbIHLCEMain%'

llrly if u wanted to Find out for Update Statement
SELECT DISTINCT OBJECT_NAME(ID) FROM syscomments where text like '%UPDATE dbo.tbIHLCEMain%'

Comments or Responses

Posted by: Sheonarayan on: 3/16/2012 Level:HonoraryPlatinum | Status: [Administrator] | Points: 10
Looks interesting !

Login to post response