Finding SP which uses particular table and column

N_Sri
N_Sri used Ask the Experts™
on
Hi ,
Please help me to write this query.
Query to find all the SPs : which use table "xyz "  and   column "abc" in that table .

Example think that there is table Employee  with Fields  Name, Age,Id, IsActive.

so this table is used in SPs  like SP_emp,Sp_Names,SP_Empee
so here SP_Emp will use table "xyz" and column "abc"  i.e SP has some code with table " xyz" and column " abc"

Well I want to have query so that I can find all the SPs with Table name as parameter and  Column Name as paramater


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Here is query you need in run in source database.

SELECT object_name(id) as 'Name', type_desc
FROM syscomments sc
Inner Join sys.objects so
ON sc.id = so.object_id
WHERE text LIKE '%%'  --> here where you pass in variable for table name of column name
and so.type IN( 'SP')

Author

Commented:
Here I want to pass two parameters  1)  table name and column name

Above solution is not working for my requirment.
Ephraim WangoyaSoftware Engineer
Commented:
try this

select object_name(id) as 'Stored Procedure'
from syscomments sc
onner join sys.objects so
on sc.id = so.object_id
where so.type = 'P'
and (text LIKE '%Employee%')
and (text LIKE '%EmpID%')
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
SELECT object_name(id) as 'Name', type_desc
FROM syscomments sc
Inner Join sys.objects so
ON sc.id = so.object_id
WHERE CONTAINS([text], 'CreateUser') --[text] LIKE '%CreateUser%'  --> here where you pass in variable for table name of column name
and so.type IN( 'SP')
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
Commented:
Select *
From    INFORMATION_SCHEMA.ROUTINES
Where   Routine_Type = 'Procedure' and
        Routine_Definition Like '%Order_Details%'

Author

Commented:
thankyou..  result set is not giving improper information

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial