logo标签列表

如何在SQL Server中检查所有存储过程是否正常？

sqlsql-serverdependenciesmetadata
43

如果我删除了一个表或字段，如何检查在SQL Server中所有存储过程是否仍然正常？

10个回答
58

我发现Cade的答案对于制定自己检查数据库中对象的脚本很有用，所以我想分享我的脚本：

DECLARE @Name nvarchar(1000);
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;

DECLARE ObjectCursor CURSOR FAST_FORWARD FOR
SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id))
FROM sys.objects o
WHERE type_desc IN (
'SQL_STORED_PROCEDURE',
'SQL_TRIGGER',
'SQL_SCALAR_FUNCTION',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_INLINE_TABLE_VALUED_FUNCTION',
'VIEW')
    --include the following if you have schema bound objects since they are not supported
    AND ISNULL(OBJECTPROPERTY(o.object_id, 'IsSchemaBound'), 0) = 0
;

OPEN ObjectCursor;

FETCH NEXT FROM ObjectCursor INTO @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = N'EXEC sp_refreshsqlmodule ''' + @Name + '''';
    --PRINT @Sql;

    BEGIN TRY
        EXEC @Result = sp_executesql @Sql;
        IF @Result <> 0 RAISERROR('Failed', 16, 1);
    END TRY
    BEGIN CATCH
        PRINT 'The module ''' + @Name + ''' does not compile.';
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    END CATCH

    FETCH NEXT FROM ObjectCursor INTO @Name;
END

CLOSE ObjectCursor;
DEALLOCATE ObjectCursor;
2
刷新触发器的唯一方法是通过更改它们（如果未加密）。 (http://connect.microsoft.com/SQLServer/feedback/details/261905/sp-refreshsqlmodule-does-not-work-with-triggers)。因此，当遇到触发器时，上述脚本会给出错误结果。 - buckley
4
不错的帖子，运行得很完美！再加一点，你需要检查对象是否绑定模式，因为 sp_refreshsqlmodule 可能会给出错误的负面结果，因为不支持绑定模式的对象。所以，请将以下内容添加到查询中：and isnull(objectproperty(o.object_id,'IsSchemaBound'),0)=0; - George Mavritsakis
3
我还会将第6行更改为SELECT SCHEMA_NAME(o.schema_id) + '.[' + OBJECT_NAME(o.object_id) + ']'，以防您的存储过程名称中有点号。 - Drew Freyling
1
@DrewFreyling 好主意！您还可以使用 QUOTENAME 来引用对象名称。我会更新答案。 - Michael Petito
3
我在一个调用了不存在表的存储过程上尝试了这个脚本，但它没有突出显示。果然，如果我只运行"sp_refreshsqlmodule 'mysp'"，它也不会报告这个存储过程。 - Amit G
显示剩余6条评论
9

它不能捕获所有内容（如动态SQL或后期绑定对象），但它是有用的 - 对于所有非模式绑定的存储过程，可以调用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
            )
1
看起来 sp_refreshsqlmodule 可能会刷新错误的模块！(https://connect.microsoft.com/SQLServer/feedback/details/656863/sp-refreshsqlmodule-corrupts-renamed-objects-definitions) - Martin Smith
@Martin 是的，我应该提到这一点。在我的情况下，因为它是一个 ALTER，并且没有另一个使用相同名称的过程，所以我得到了一个错误，但它并没有造成任何损害。 - Cade Roux
完美运行，为我节省了大量时间。 - BunkerBilly
我刚刚发现一个存储过程引用了一个不存在的数据库，但是通过了 sp_refreshsqlmodule。 - Nick.McDermaid
7

我基本上做了相同的事情，但是写成了无需游标的形式，这样速度会更快。

DECLARE @Name nvarchar(1000);
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;

DECLARE @Objects TABLE (
    Id INT IDENTITY(1,1),
    Name nvarchar(1000)
)

INSERT INTO @Objects
SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id))
FROM sys.objects o
WHERE type_desc IN (
'SQL_STORED_PROCEDURE',
'SQL_TRIGGER',
'SQL_SCALAR_FUNCTION',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_INLINE_TABLE_VALUED_FUNCTION',
'VIEW')
    --include the following if you have schema bound objects since they are not supported
    AND ISNULL(OBJECTPROPERTY(o.object_id, 'IsSchemaBound'), 0) = 0

DECLARE @x INT
DECLARE @xMax INT

SELECT @xMax = MAX(Id) FROM @Objects
SET @x = 1

WHILE @x < @xMax
BEGIN
    SELECT @Name = Name FROM @Objects WHERE Id = @x

    SET @Sql = N'EXEC sp_refreshsqlmodule ''' + @Name + '''';
    --PRINT @Sql;

    BEGIN TRY
        EXEC @Result = sp_executesql @Sql;
        IF @Result <> 0 RAISERROR('Failed', 16, 1);
    END TRY
    BEGIN CATCH
        PRINT 'The module ''' + @Name + ''' does not compile.';
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    END CATCH
    SET @x = @x + 1
END
3
使用while语句循环遍历数据表与使用带有fast_forward的光标基本上是相同的。两者都是逐行进行操作（RBAR）。这是一个可以接受的情况，因为它不能被集合操作所取代。 - Nicholas
这比使用游标执行要好一些，即使它仍然使用 while 循环。 - Dan Field
6
除了Michael Petito的脚本之外，您还可以像这样检查SP中延迟绑定对象（延迟名称解析）的问题：
-- Based on comment from http://blogs.msdn.com/b/askjay/archive/2012/07/22/finding-missing-dependencies.aspx
-- Check also http://technet.microsoft.com/en-us/library/bb677315(v=sql.110).aspx

select o.type, o.name, ed.referenced_entity_name, ed.is_caller_dependent
from sys.sql_expression_dependencies ed
join sys.objects o on ed.referencing_id = o.object_id
where ed.referenced_id is null
问题在于它仍会将CTE别名和临时表作为空引用返回。 - John Zabroski
1
这会捕获传递 sp_refreshsqlmodule 的存储过程，即使其中的对象无效。 - Nick.McDermaid
3
一旦我对表进行更改，例如列重命名，就必须修改所有引用该表列的存储过程、函数和视图。显然，我必须逐个手动修改它们。但是我的数据库包含了数百个这样的对象。因此，我想确保我已经修改了所有依赖的对象。一个解决方案是重新编译所有对象（通过脚本）。但是重新编译只会在每个对象的下一次执行时发生。但我想要的是立即验证并获取详细信息。
为此，我可以使用“sp_refreshsqlmodule”而不是“sp_recompile”。这将刷新每个对象，并在其无法正确解析时抛出错误。 以下是脚本：
 -- table variable to store procedure names
    DECLARE @tblObjects TABLE (ObjectID INT IDENTITY(1,1), ObjectName 
    sysname)

   -- get the list of stored procedures, functions and views
    INSERT INTO @tblObjects(ObjectName)
    SELECT '[' + sc.[name] + '].[' + obj.name + ']'
    FROM sys.objects obj
    INNER JOIN sys.schemas sc ON sc.schema_id = obj.schema_id
    WHERE obj.[type] IN ('P', 'FN', 'V') -- procedures, functions, views

    -- counter variables
    DECLARE @Count INT, @Total INT
    SELECT @Count = 1
    SELECT @Total = COUNT(*) FROM @tblObjects

    DECLARE @ObjectName sysname

    -- start the loop
    WHILE @Count <= @Total BEGIN

    SELECT @ObjectName = ObjectName
    FROM @tblObjects
    WHERE ObjectID = @Count

    PRINT 'Refreshing... ' + @ObjectName

    BEGIN TRY
        -- refresh the stored procedure
        EXEC sp_refreshsqlmodule @ObjectName
    END TRY
    BEGIN CATCH
        PRINT 'Validation failed for : ' + @ObjectName + ', Error:' + 
        ERROR_MESSAGE() + CHAR(13)
    END CATCH

    SET @Count = @Count + 1

    END

如果任何对象抛出错误，我现在可以处理它并手动修复问题。最初的回答。
2

没有一个给出的答案能够找到由于重命名或删除表而导致的错误，但是高兴的是，在SQL Server 2017及更高版本中有解决方案：

"最初的回答"

DECLARE @NumberRecords INT
DECLARE @RowCount INT
DECLARE @Name NVARCHAR(MAX)
DECLARE @Command NVARCHAR(MAX)
DECLARE @Result int
DECLARE @Names TABLE (
    [RowId] INT NOT NULL    IDENTITY(1, 1),
    [Name]  NVARCHAR(MAX),
    [Type]  NVARCHAR(MAX)
)

INSERT INTO @Names
SELECT
        QUOTENAME(SCHEMA_NAME([Objects].schema_id)) + '.' + QUOTENAME(OBJECT_NAME([Objects].object_id)) [Name],
        type_desc [Type]
FROM sys.objects [Objects]
WHERE type_desc IN ('SQL_STORED_PROCEDURE',
                    'SQL_TRIGGER',
                    'SQL_SCALAR_FUNCTION',
                    'SQL_TABLE_VALUED_FUNCTION',
                    'SQL_INLINE_TABLE_VALUED_FUNCTION',
                    'VIEW')
ORDER BY [Name]

SET @RowCount = 1
SET @NumberRecords = (SELECT COUNT(*) FROM @Names)
WHILE (@RowCount <= @NumberRecords)
BEGIN

    SELECT @Name = [Name]
    FROM @Names
    WHERE [RowId] = @RowCount

    SET @Command = N'EXEC sp_refreshsqlmodule ''' + @Name + ''''

    BEGIN TRY

        EXEC @Result = sp_executesql @Command

        IF @Result <> 0
        BEGIN

            RAISERROR('Failed', 16, 1)

        END
        ELSE
        BEGIN

            IF (NOT EXISTS (SELECT *
                            FROM sys.dm_sql_referenced_entities(@Name, 'OBJECT')
                            WHERE [is_incomplete] = 1))
            BEGIN

                DELETE
                FROM @Names
                WHERE [RowId] = @RowCount

            END

        END

    END TRY
    BEGIN CATCH

        -- Nothing

    END CATCH

    SET @RowCount = @RowCount + 1

END

SELECT  [Name],
        [Type]
FROM @Names
“is_incomplete” 在我的 “sys.dm_sql_referenced_entities” 中似乎不存在，我尝试了您的脚本，使用了 “is_all_columns_found”，但它返回了太多的错误结果... - Louis Somers
这似乎更有效： IF (NOT EXISTS (SELECT 1 FROM sys.dm_sql_referenced_entities(@Name, 'OBJECT') WHERE is_all_columns_found = 0 AND referenced_minor_name IS NOT null)) - Louis Somers
我在 SQL Server 2017 上编写和测试了这个脚本。 - user1297556
2

以下是几种可行的方法：

  1. 最明显的方式是运行这些过程。
  2. 在删除表或字段之前，请检查它们的依赖关系，然后查看那些依赖的过程。
  3. 生成所有过程的脚本，并搜索该字段或表。
  4. 查询sysobjects。
1
我尝试了“Cade Roux”的回答，但出现了错误，我按照以下方式进行了修复。
 SELECT 'BEGIN TRAN T1;' UNION
    SELECT   REPLACE('BEGIN TRY
    EXEC sp_refreshsqlmodule ''{OBJECT_NAME}''
      END TRY
      BEGIN CATCH
    PRINT ''{OBJECT_NAME} IS INVALID.'' 
     END CATCH', '{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
                              UNION 
                            SELECT  'ROLLBACK TRAN T1;'
1

同样的想法，但更加通用 - 您检查所有带有主体的用户定义对象，并在编译时显示错误。在重命名/删除对象/列等之后，这真的非常有用。

只需在数据库模式更新后运行它，以确保所有主体对象仍然有效。

DECLARE @obj_name AS sysname, @obj_type AS sysname

DECLARE obj_cursor CURSOR FOR 
    SELECT SCHEMA_NAME(o.schema_id) + '.' + o.name, o.type_desc 
    FROM sys.objects o 
    INNER JOIN sys.sql_modules m ON o.object_id = m.object_id
    WHERE o.is_ms_shipped = 0 AND m.is_schema_bound = 0 
    ORDER BY o.type_desc, SCHEMA_NAME(o.schema_id), o.name

OPEN obj_cursor 
FETCH NEXT FROM obj_cursor INTO @obj_name, @obj_type

WHILE (@@FETCH_STATUS <> -1) 
BEGIN
    BEGIN TRY
        EXEC sp_refreshsqlmodule @obj_name
        --PRINT 'Refreshing ''' + @obj_name + ''' completed'
    END TRY
    BEGIN CATCH
        PRINT 'ERROR - ' + @obj_type + ' ''' + @obj_name + ''':' + ERROR_MESSAGE()
    END CATCH
    FETCH NEXT FROM obj_cursor INTO @obj_name, @obj_type
END 

CLOSE obj_cursor
DEALLOCATE obj_cursor
是的，这更好，因为它实际上显示了原始错误。 - Polyfun
1
它没有识别出已被删除的表。 - Nitesh
0

我的做法有点不同。我创建了一堆存储过程的 alter 脚本在 SSMS 中，并等待几秒钟让 SSMS 处理它们，然后我就得到了我想要的结果：

enter image description here

在SSMS中，如果有任何错误的行，右边框会显示一个红点，我可以轻松地检查、更正并稍后执行相同的脚本以更新正确的值。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接