Find Stored Procedure that Inserts Into a Specific Table

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 Stor开发者_Python百科ed 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

---

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'

---

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 

---

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.

---

I needed to know what the actual object id is, i.e. Stored Procedure / Trigger, etc.

So thanks to JNK, I altered the script to show it to me like this:

SELECT OBJECT_NAME(object_id)   name
,case   xtype   when 'AF'   then 'Aggregate Function (CLR)'
                when 'C'    then 'CHECK Constraint'
                when 'D'    then 'Default or DEFAULT Constraint'
                when 'F'    then 'FOREIGN KEY Constraint'
                when 'L'    then 'Log'
                when 'FN'   then 'Scalar Function'
                when 'FS'   then 'Assembly (CLR) scalar-Function'
                when 'FT'   then 'Assembly (CLR) Table-valued Function'
                when 'IF'   then 'In-lined Table-Function'
                when 'IT'   then 'Internal Table'
                when 'P'    then 'Stored Procedure'
                when 'PC'   then 'Assembly (CLR) Stored-Procedure'
                when 'PK'   then 'PRIMARY KEY Constraint (Type is K)'
                when 'RF'   then 'Replication filter Stored Procedure'
                when 'S'    then 'System Table'
                when 'SN'   then 'Synonym'
                when 'SQ'   then 'Service Queue'
                when 'TA'   then 'Assembly (CLR) DML Trigger'
                when 'TF'   then 'Table Function'
                when 'TR'   then 'SQL DML Trigger'
                when 'TT'   then 'Table Type'
                when 'U'    then 'User Table'
                when 'UQ'   then 'UNIQUE Constraint (Type is K)'
                when 'V'    then 'View'
                when 'X'    then 'Extended Stored Procedure'
                else ''     end [xtype description]
FROM sys.sql_modules, sysobjects
where   sql_modules.object_id = sysobjects.id
and definition LIKE '%InvNum%'
order by xtype,OBJECT_NAME(object_id)

Results show like this:

---

List's top 10 stored procedure executed that do insert/update on a table by last execution time you can you use below script

GRANT VIEW SERVER STATE TO databaseuser

Select top 10 dest.objectid,  DB_Name(dest.[dbid]) As 'databaseName'
, Object_Name(dest.objectid, dest.[dbid]) As 'procName'
, Max(deqs.last_execution_time) As 'last_execution'
From sys.dm_exec_query_stats As deqs
Cross Apply sys.dm_exec_sql_text(deqs.sql_handle) As dest
Join sys.sql_dependencies as sqldep on sqldep.object_id = dest.objectid
Where dest.[text] Like '%CashProduct%' -- replace
And dest.[dbid] Is Not Null  -- exclude ad-hocs
And DB_Name(dest.[dbid]) = 'DatabaNameDu'
And sqldep.is_updated = 1
Group By db_name(dest.[dbid])
, Object_Name(dest.objectid, dest.[dbid]),
dest.objectid
Order By 
Max(deqs.last_execution_time) desc
Option (MaxDop 1);