Sometime as a database developer or DBA, we need to find all stored procedures in a database in which a specific table is being used. We can get this important information with the help of sys.objects and syscomments system objects.
Following is the query to retrieve name of all stored procedure using table object of name “Customer” in Selected Database.
IN SQL Server 2000
Select Distinct O.name, O.type_desc
from sys.objects O Join syscomments C
on O.object_id = C.id
where C.text like ‘%Customer%’
IN SQL Server 2005
Select O.name, O.type_desc
from sys.objects O
Where OBJECT_DEFINITION(O.object_id) like ‘%Customer%’