Как найти все хранимые процедуры, которые вставляют, обновляют или удаляют записи?

Question

Можно ли без разбора источника выбрать список всех имен sproc, которые вставляют, обновляют или удаляют записи? Мне нужно создать служебный скрипт TSQL, который будет делать это. Эффективность не является проблемой, потому что она будет запускаться только несколько раз в год (Curse'rs, я имею в виду, с курсорами все в порядке). В идеале этот скрипт не должен включать обновления таблиц временных или локальных переменных.

Я попробовал следующий запрос, найденный на 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 

Но это производит ложные негативы.

Answer 1

Вызовите 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
            )

Answer 2

Это проблема с sys.sql_dependencies. SQL не может точно отследить зависимости в хранимых процедурах (есть веские причины, по которым он не может этого сделать, но сейчас не пойдет туда). Это даже без учета динамических процедур SQL или CLR.

Visual Studio Database Edition обладает некоторыми лучшими возможностями, но он может отслеживать зависимости в сценариях, а не в действующей базе данных. Он может, однако, преобразовывать действующие базы данных в сценарии и анализировать полученные сценарии с некоторой лучшей точностью, чем sys.sql_dependencies. Он не может обрабатывать динамический SQL.

Answer 3

Благодаря ответу Кейд Ру я смог очень близко с этим разобраться:

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