Find a stored procedure that inserts into a specific table

Question

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

Stored procedure A inserts into table A
Saved Proceudre B inserts into table A
Stored Procedure C Inserts into Table B

I want the query to return the name of stored procedure A and stored procedure B.

I got it right now, but all it does is find stored procedures. I think that would be a good starting point for finding 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 for sys.sql_modules , which contains the text of all the processing and views:

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

If you are confident in the wording, you may find something like 'INSERT INTO mytable'

Answer 2

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

Searching for stored procedures containing this name can lead to several false positives if many options refer to the table.

sys.sql_dependencies deprecated, but may be useful here because it contains the is_updated flag, which is also set to 1 for insertions.

 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 aren't you using the following 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 any procedure with specific text in it.