-Suppose we have a EmpDetails table in our database and there are certain number of stored procedures in database. We want to know in which stored procedure(s) table EmpDetails is used.

-We can use following query

SELECT

sys.objects.name, sys.objects.type, sys.objects.type_desc,

sys.objects.schema_id, sys.syscomments.text

FROM sys.objects

INNER JOIN sys.syscomments ON sys.objects.object_id = sys.syscomments.id

where sys.syscomments.text like '%EmpDetails%'

And type ='P'