验证所有存储过程是否有效。

Question

背景
我的应用程序由SQL Server（2008 R2）支持，并且有相当多的SP、触发器等。
我的目标是确保在程序启动时，所有这些对象仍然有效。
例如，如果我有一个存储过程A调用存储过程B，如果有人将B的名称更改为C，我希望在Debug环境中运行我的应用程序时得到通知。

我尝试了什么？
因此，我想使用sp_refreshsqlmodule，根据文档，它返回0（成功）或非零数（失败）：

DECLARE @RESULT int 
exec @RESULT  = sp_refreshsqlmodule N'A' --In this case A is the SP name
SELECT @@ERROR
SELECT @RESULT 

因此，我将SP的B名称更改为C，并运行了脚本。 结果如下：

• @@ERROR是0
• @RESULT是0
• 我收到了以下信息：

模块“A”依赖于缺失对象“B”。该模块仍然会被创建；但是，在该对象存在之前，它无法成功运行。

我的问题：
这里是否有遗漏的内容？难道我不应该得到一个非零数字来指示出现了错误吗？

- Avi Turner

---

尽管该消息提到了问题，但它也表明：“模块仍将被创建”。这对我来说听起来并不像是“失败”，因此我认为在这种情况下sp_refreshsqlmodule返回0没有任何问题。 - Andriy M

@AndriyM 我会期望它返回一些非零值，表明并不是所有的东西都100%正常... - Avi Turner

嗯，那可能会有所帮助，但手册明确指出非零结果表示失败，而不是“不是一切都100％正常”。显然，您和手册在失败问题上有不同的看法。 :) - Andriy M

似乎与停机问题有关。考虑到您可能在存储过程中使用动态SQL，我认为这并不总是一个合理的问题。 - Aron

Answer 1

假设您的所有依赖项至少具有模式限定符，那么似乎可以使用sys.sql_expression_dependencies。例如，运行以下脚本:

create proc dbo.B
as
go
create proc dbo.A
as
exec dbo.B
go
select OBJECT_SCHEMA_NAME(referencing_id),OBJECT_NAME(referencing_id),
   referenced_schema_name,referenced_entity_name,referenced_id
from sys.sql_expression_dependencies
go
sp_rename 'dbo.B','C','OBJECT'
go
select OBJECT_SCHEMA_NAME(referencing_id),OBJECT_NAME(referencing_id),
   referenced_schema_name,referenced_entity_name,referenced_id
from sys.sql_expression_dependencies

< p > sql_expression_dependencies 的第一个查询结果显示其依赖项为：

(No Column name) (No Column name) referenced_schema_name referenced_entity_name referenced_id
dbo              A                dbo                    B                      367340373

重命名后，第二个查询的结果如下：

(No Column name) (No Column name) referenced_schema_name referenced_entity_name referenced_id
dbo              A                dbo                    B                      NULL

也就是说，referenced_id 为 NULL。因此，这个查询可以找到所有损坏的存储过程（或其他可能包含引用的对象）：

select OBJECT_SCHEMA_NAME(referencing_id),OBJECT_NAME(referencing_id)
from
    sys.sql_expression_dependencies
group by
    referencing_id
having SUM(CASE WHEN referenced_id IS NULL THEN 1 ELSE 0 END) > 0

- Damien_The_Unbeliever

---

@AviTurner - 这个视图中的每一行都代表一个对象所依赖的依赖项。因此，我所指的行是从 dbo.A 到一个名为 dbo.B 的对象的依赖项行。在第二个选择中，这个引用被打破了。在这个视图中，没有任何关于没有依赖项的 dbo.B 存储过程的行。 - Damien_The_Unbeliever

我会在有机会的时候更仔细地检查你的解决方案。从第一眼看，当我运行它时，似乎它给了我许多看起来是误报的结果。 - Avi Turner

我的第一个假设，在第一句话中，是正确的吗？你的依赖关系是否有模式限定？因为如果你只有 exec B，在运行时就无法知道是否存在有效的 B。 - Damien_The_Unbeliever

抱歉之前没有注意到，但我认为我只使用了默认模式。这样可以吗？在您发布的脚本中，对于OBJECT_SCHEMA_NAME（referencing_id），我只得到dbo，而对于referenced_schema_name，我得到dbo或NULL。 - Avi Turner

@AviTurner：在那之前，请将它们视为已损坏的。 :) 始终指定模式确实是一个好主意。 - Andriy M

Answer 2

你可以尝试这个方法。虽然它可能不完全适用于架构（下面有所有者名称），因为它更多是基于我使用SQL Server 2000时的情况，但我在2008年进行了测试，它基本上在所有过程、函数和视图上运行alter语句。将PRINT @objName + ' seems valid.'注释掉，只查看无效的过程、函数和视图... 随意编辑任何你想修改的部分！

DECLARE @objId INT
DECLARE @objName NVARCHAR(max)
DECLARE @owner NVARCHAR(255)
DECLARE @Def nvarchar(max)

DECLARE checker CURSOR FAST_FORWARD FOR
    SELECT
        id, name, USER_NAME(o.uid) owner 
    FROM sysobjects o 
    WHERE   o.type IN ('P', 'TR', 'V', 'TF', 'FN', 'IF')
            AND o.name <> 'RecompileSQLCode'

OPEN checker
FETCH FROM checker INTO @objId, @objName, @owner

WHILE @@FETCH_STATUS=0
BEGIN
    SELECT @Def = definition      
    FROM sys.sql_modules 
    WHERE object_id = @objId


       --print @objName
       --print @def
       SET @def = REPLACE(@def, 'create procedure','alter procedure')
       SET @def = REPLACE(@def, 'create PROC','alter PROC')
       SET @def = REPLACE(@def, 'create trigger','alter trigger')
       SET @def = REPLACE(@def, 'create function','alter function')
       SET @def = REPLACE(@def, 'create view','alter view')
    BEGIN TRANSACTION
        BEGIN TRY
            EXEC sp_executesql @def
            PRINT @objName + ' seems valid.'
        END TRY
        BEGIN CATCH
                PRINT 'Error: ' + @objName + ' : ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + ' ' + ERROR_MESSAGE()
        END CATCH
    ROLLBACK

    FETCH NEXT FROM checker INTO @objId, @objName, @owner
END

CLOSE checker
DEALLOCATE checker

- MacWise

---

请告诉我您是否能够测试它。我很想听取您的反馈意见。谢谢！ - MacWise

更新：首先，我必须说，这是一段不错的代码...我稍微修改了它（请参见帖子的编辑）。我仍然遇到一些错误。我会检查它们是真正的问题还是误报（错误是：“错误：xyz：2714 数据库中已经存在名为'xyz'的对象。”）。我会向您更新结果。 - Avi Turner

感谢您的反馈，啊，我可能应该为每种类型都放置一个REPLACE，比如“create trigger”，“alter trigger”，以及每种类型的函数也应该在IN子句中列出。 - MacWise

好的，我已经检查了你的解决方案。尽管我们已经将 B 重命名为 C，但是在表 sys.sql_modules 中 C 的 definition 值仍然是 CREATE PROC B。因此，当您尝试更改 SP C 时，这将导致测试失败...不过还是谢谢你，你的努力很好。 - Avi Turner

Answer 3

这是一个过程，可以将服务器上的所有存储过程脚本化为带有后缀的CREATE过程。该脚本创建了对“TEMP / Test”过程的相应DROP PROCEDURE。正常创建存储过程时，这不会确认存储过程是否引用了无效的表名，因为它不会验证此内容。

BEGIN TRAN

--Creating temp able with copy of all procedures
 DECLARE @tTempProcedures TABLE
 (
    ProcedureName NVARCHAR(MAX),
    OriginalProcCreateSQL NVARCHAR(MAX),
    CreateNewProcSQL NVARCHAR(MAX),
    DropTestProcedureSQL NVARCHAR(MAX),
    AllInOneSQL NVARCHAR(MAX)
 )

 INSERT INTO @tTempProcedures
 SELECT 
         procedures.name                                AS  ProcedureName         
        ,syscomments.Text                               AS  OriginalProcCreateSQL
        ,REPLACE(syscomments.Text
                ,procedures.name
                ,procedures.name + '_TEST_CREATE')
            + ' GO'                                     AS  CreateNewProcSQL
        ,'DROP PROCEDURE ' 
            + procedures.name 
            + '_TEST_CREATE'                         AS  DropTestProcedureSQL

    ,'EXEC sp_executesql ' +''''''+
        REPLACE(
            REPLACE(syscomments.Text
                    ,procedures.name
                    ,procedures.name + '_TEST_CREATE')
                ,''''
                ,'''''')

        +''''''
        +  CHAR(10) + CHAR(13) 

        +  CHAR(10) + CHAR(13) 
        + 'EXEC sp_executesql ' +''''''+ 'DROP PROCEDURE ' 
        +  procedures.name 
        +  '_TEST_CREATE' +''''''
        +  CHAR(10) + CHAR(13)                  
                                                AS  AllInOneSQL
FROM
    syscomments

    Inner Join sys.procedures
    ON syscomments.id = procedures.OBJECT_ID






DECLARE cur CURSOR FOR 
SELECT AllInOneSQL FROM @tTempProcedures 

OPEN cur
DECLARE @AllInOneSQL NVARCHAR(MAX)
FETCH NEXT FROM cur INTO @AllInOneSQL

WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT(@AllInOneSQL)
    EXEC sp_executesql @AllInOneSQL

    FETCH NEXT FROM cur INTO @AllInOneSQL
END

CLOSE cur
DEALLOCATE cur


ROLLBACK

警告：请谨慎使用任何DROP PROCEDURE语句。

注意：您还可以使用：“SET NOEXEC ON”，然后执行该过程。如果该过程无效，则会出现错误。如果该过程有效，则在设置“SET NOEXEC ON”后不会更新任何记录。但是，这很难自动化，因为您需要使用有效的参数调用过程。

- Richard Vivian

---

感谢您的输入。起初，您的解决方案看起来很有前途，我修改了AllInOneSQL，用EXEC sp_executesql包装了create和drop语句，而不是使用GO（EXEC sp_executesql无法执行带有GO的语句）。但后来我遇到了一个问题，即过程中有一个省略号（'），这破坏了我的脚本。我已经编辑了您的脚本，以便您可以看到问题。如果我在您的意图中漏掉了什么，请随时回滚。 - Avi Turner

我喜欢光标和方法。这使得脚本更有用。我还考虑将更好的错误处理添加到过程中。在进一步测试中，我还注意到它无法检测到过程引用不存在的列和表。我认为这是验证过程中的限制。我计划在我们的站点中添加一个日志记录功能到所有过程中，以便将无效的过程调用的时间和位置显示出来。虽然不如我所希望的那样主动，但这是我的处理方式。 - Richard Vivian

Answer 4

确实很奇怪，我自己尝试过，sp_refreshsqlmodule 的结果并不一致。更奇怪的是，如果出现错误，事务仍然保持打开状态，这就是我添加 ROLLBACK TRAN 的原因。这里有一个替代方案：

DECLARE
    @is_refresh_ok AS BIT = 0
    , @error_message VARCHAR(MAX)

BEGIN TRY       
    EXEC sp_refreshsqlmodule '<SP name here>'       
    SET @is_refresh_ok = 1
END TRY
BEGIN CATCH
    SET @error_message = ERROR_MESSAGE()

    IF @@TRANCOUNT > 0
    ROLLBACK TRAN
END CATCH   

SELECT @is_refresh_ok, @error_message

如果需要的话，这里有一个脚本可以自动刷新数据库中所有存储过程和函数。

- bjnr

---

谢谢您的回答。这大致是我的脚本样子。我发帖问问题的原因是它没有给出预期的结果... - Avi Turner

Answer 5

SELECT
  OBJECT_NAME(referencing_id) AS [this sproc or view...],
  referenced_entity_name AS [... depends on this missing entity name]
FROM sys.sql_expression_dependencies
WHERE is_ambiguous = 0
  AND OBJECT_ID(referenced_entity_name) IS NULL
  AND referenced_entity_name NOT IN
    (SELECT Name FROM sys.types WHERE is_user_defined = 1)
ORDER BY OBJECT_NAME(referencing_id), referenced_entity_name

希望这能有所帮助。 -托马斯

- RosSQL

Answer 6

当执行CREATE PROCEDURE语句时，SP的文本被解析，但外部名称解析被推迟到运行时。这允许对象之间存在循环依赖关系，并避免了必须按照特定结构编写发布脚本的必要性。这里是有关此主题的Technet链接。我可以看出sp_refreshsqlmodule如何重新解析SP的文本并成功提取其元数据，报告0，但仍未将其绑定到相关对象。这是另一个处理该主题的SO问题。

在其他情况下，我已经成功使用SQL解析器（SO问题这里和这里）。您可以捕获EXEC语句并列出相关的SP名称。

- Michael Green