Find Stored Procedure that Inserts Into a Specific Table

Question

Is there a way to find what stored procedures create an entry in a table. Say for example:

Stored Procedure A inserts into Table A
Stored Proceudre B Inserts into Table A
Stored Procedure C Inserts into Table B

I want to the query to return the name of Stored Procedure A and Stored Procedure B.

Ive got this right now, but all it does is find Stored Procedures. I think it would be a good starting point to find the stored procedures.

select schema_name(schema_id) as [schema], 
       name
from sys.procedures
where name like '%Item%' and name like '%Create%'

I am using Microsoft SQL 2008

Answer 1

You can search sys.sql_modules which contains the text of all the procs and views:

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%TablenameIWanttoFind%'

If you are certain of the wording you can search for something like 'INSERT INTO mytable'

Answer 2

The question asks how to find a stored procedure that inserts into a specific table.

Searching for stored procedures containing the name may bring back quite a few false positives if the table is referenced for many selects.

sys.sql_dependencies is deprecated but can be useful here as it contains an is_updated flag that also is set to 1 for inserts.

SELECT QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id))
FROM   sys.sql_dependencies
WHERE  referenced_major_id = OBJECT_ID('YourTable')
       AND is_updated = 1
GROUP  BY object_id 

Answer 3

Why don't you use the below query.

select O.name from sysobjects O
Join Syscomments  C
on O.id=C.id
Where C.text like '%insert into%Table_name%'

From this query you can literally find anything procedure with a specific text in it.