SQL 查找插入特定表的存储过程

Find Stored Procedure that Inserts Into a Specific Table

提问by user489041

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

存储过程 A 插入表 A
存储过程 B 插入表 A
存储过程 C 插入表 B

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

我想查询返回存储过程 A 和存储过程 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

我正在使用 Microsoft SQL 2008

回答by JNK

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

您可以搜索sys.sql_modules其中包含所有过程和视图的文本：

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'

如果您确定措辞，您可以搜索类似的内容 'INSERT INTO mytable'

回答by Martin Smith

The question asks how to find a stored procedure that insertsinto 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_dependenciesis deprecated but can be useful here as it contains an is_updatedflag that also is set to 1for inserts.

sys.sql_dependencies已弃用，但在此处很有用，因为它包含一个is_updated也设置1为插入的标志。

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 

回答by Praveen Valavan

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.

从这个查询中，您可以从字面上找到任何包含特定文本的过程。

回答by Birel

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

我需要知道实际的对象 id 是什么，即存储过程/触发器等。

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

所以多亏了 JNK，我修改了脚本，像这样显示给我：

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:

结果显示如下：

回答by Nayas Subramanian

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

列出执行的前 10 个存储过程，它们在上次执行时间之前对表进行插入/更新，您可以使用下面的脚本

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);