来自表的存储过程列表 [英] List of Stored Procedure from Table

问题描述

我有一个包含 100 个表和存储过程的庞大数据库.使用 SQL Server 2005，如何获取对给定表执行插入或更新操作的存储过程列表.

I have a huge database with 100's of tables and stored procedures. Using SQL Server 2005, how can I get a list of stored procedures that are doing an insert or update operation on a given table.

推荐答案

select
  so.name,
  sc.text
from
  sysobjects so inner join syscomments sc on so.id = sc.id
where
  sc.text like '%INSERT INTO xyz%'
  or sc.text like '%UPDATE xyz%'

这将为您提供一个包含特定表的 INSERT 或 UPDATE 的所有存储过程内容的列表(您显然可以调整查询以适应).此外，较长的过程将在返回的记录集中的多行中分解，因此您可能需要对结果进行一些手动筛选.

This will give you a list of all stored procedure contents with INSERT or UPDATE in them for a particular table (you can obviously tweak the query to suit). Also longer procedures will be broken across multiple rows in the returned recordset so you may need to do a bit of manual sifting through the results.

编辑:调整查询以返回 SP 名称.另请注意，上述查询将返回任何 UDF 和 SP.

Edit: Tweaked query to return SP name as well. Also, note the above query will return any UDFs as well as SPs.

这篇关于来自表的存储过程列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！