代码之家 › 专栏 › 技术社区 › Tim Santeford

如何查找插入、更新或删除记录的所有存储过程?

tsql sql-server

Tim Santeford · 技术社区 · 16 年前

在不分析源代码的情况下,是否可以选择插入、更新或删除记录的所有存储过程名称的列表?我需要创建一个TSQL实用程序脚本来实现这一点。效率不是问题,因为它一年只运行几次(诅咒的意思是诅咒是可以的)。理想情况下,该脚本不包括对临时或局部变量表的更新。

我尝试了在上找到的以下查询 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

但它会产生错误的否定。

3 回复 | 直到 16 年前

Cade Roux 16 年前

呼叫 sp_refreshsqlmodule 在所有非架构绑定存储过程上:

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
            )

Remus Rusanu 16 年前

这就是sys.sql_依赖项的问题。SQL无法准确地跟踪存储过程中的依赖项(这有充分的理由说明它不能跟踪,但现在不去跟踪)。这甚至没有考虑动态SQL或CLR过程。

Visual Studio数据库版本有一些更好的功能,但它可以跟踪scipts中的依赖项,而不是实时数据库中的依赖项。它可以将实时数据库逆向工程到脚本中,并分析结果脚本,与sys.sql_依赖项相比,具有更好的准确性。它无法处理动态SQL。

Tim Santeford 16 年前

多亏了凯德·鲁克斯的回答,我才非常接近这一点:

DECLARE @RoleName nvarchar(255)
SET @RoleName = 'READONLYUSER'

DECLARE @ROUTINE_NAME nvarchar(255)
DECLARE RoutineList Cursor FOR
SELECT 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)
    AND NOT ROUTINE_NAME like 'sel%'
    AND NOT ROUTINE_NAME like 'sp_upd%'
    AND NOT ROUTINE_NAME like 'sp_sel%'
    AND NOT ROUTINE_NAME like 'sp_ins%' 
OPEN RoutineList
FETCH NEXT FROM RoutineList 
INTO @ROUTINE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC ('EXEC sp_refreshsqlmodule ''' + @ROUTINE_NAME + '''')

    FETCH NEXT FROM RoutineList INTO @ROUTINE_NAME
END
CLOSE RoutineList
DEALLOCATE RoutineList


DECLARE GrantList Cursor FOR
SELECT DISTINCT
    so.name AS ROUTINE_NAME
FROM sysobjects so
LEFT JOIN (
    SELECT 
        so.name,
        so2.name AS [table],
        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'
        and is_updated = 1
) Updates ON so.name = Updates.name
WHERE 
    so.xtype = 'p' -- procedure
    AND Updates.name is null
    AND so.name NOT LIKE '%[_]%'
ORDER BY so.name

OPEN GrantList
FETCH NEXT FROM GrantList 
INTO @ROUTINE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN

    print 'GRANT EXECUTE ON [dbo].['+@ROUTINE_NAME+'] TO ['+@RoleName +']   '  


    FETCH NEXT FROM GrantList INTO @ROUTINE_NAME
END
CLOSE GrantList
DEALLOCATE GrantList