如何查找所有插入、更新或删除记录的存储过程? [英] How do I find all stored procedures that insert, update, or delete records?

问题描述

是否可以在不解析源的情况下选择插入、更新或删除记录的所有 sproc 名称的列表?我需要创建一个 TSQL 实用程序脚本来执行此操作.效率不是问题，因为它一年只会运行几次(Curse'rs 我的意思是 Cursors 没问题).理想情况下，此脚本不包括对临时或局部变量表的更新.

Is it possible, without parsing source, to select a list of all sproc names that insert, update, or delete records? I need to create a TSQL utility script that will do this. Efficiency is not an issue because it will be run only a few times a year (Curse'rs I mean Cursors are ok). Ideally this script would not include updates to temp or local variable tables.

我尝试了在 SO Question 上找到的以下查询.

I tried the following query found on SO Question.

SELECT 
 so.name,
 so2.name,
 sd.is_updated
 from sysobjects so
 inner join sys.sql_dependencies sd on so.id = sd.object_id
 inner join sysobjects so2 on sd.referenced_major_id = so2.id
where so.xtype = 'p' -- procedure
 and 
is_updated = 1 -- proc updates table, or at least, I think that's what this means 

但它会产生假阴性.

推荐答案

调用 sp_refreshsqlmodule 在所有非模式绑定的存储过程上:

Call sp_refreshsqlmodule on all non-schema bound stored procedures:

DECLARE @template AS varchar(max)
SET @template = 'PRINT ''{OBJECT_NAME}''
EXEC sp_refreshsqlmodule ''{OBJECT_NAME}''

'

DECLARE @sql AS varchar(max)

SELECT  @sql = ISNULL(@sql, '') + REPLACE(@template, '{OBJECT_NAME}',
                                          QUOTENAME(ROUTINE_SCHEMA) + '.'
                                          + QUOTENAME(ROUTINE_NAME))
FROM    INFORMATION_SCHEMA.ROUTINES
WHERE   OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
                                 + QUOTENAME(ROUTINE_NAME)),
                       N'IsSchemaBound') IS NULL
        OR OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
                                    + QUOTENAME(ROUTINE_NAME)),
                          N'IsSchemaBound') = 0

        EXEC (
              @sql
            )

这篇关于如何查找所有插入、更新或删除记录的存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！