代码之家 › 专栏 › 技术社区 › Cá´ÊÊ bcherry

验证T-SQL存储过程的可靠方法

syntax sql-server-2008 tsql sql-server sql

Cá´ÊÊ bcherry · 技术社区 · 15 年前

我们正在从SQL Server 2005升级到2008。几乎2005实例中的每个数据库都设置为2000兼容模式,但我们现在跳到2008。我们的测试已经完成了,但我们学到的是,我们需要更快地进行测试。

我发现了一些存储过程,要么从丢失的表中选择数据,要么尝试按不存在的列排序。

包装SQL以在set parseonly on中创建过程,在try/catch中捕获错误仅捕获order bys中的无效列。在从丢失的表中选择数据的过程中,它找不到错误。然而,ssms 2008的intellisense确实发现了这个问题,但是我仍然可以继续并成功地运行该过程的alter脚本,而不必抱怨。

那么,为什么我甚至可以不去创建一个在运行时失败的过程呢?有没有比我试过的更好的工具?

我发现的第一个工具不是很有用: DbValidator from CodeProject 但是它发现的问题比我在sqlservercentral上发现的这个脚本少,后者发现了无效的列引用。

-------------------------------------------------------------------------
-- Check Syntax of Database Objects
-- Copyrighted work.  Free to use as a tool to check your own code or in 
--  any software not sold. All other uses require written permission.
-------------------------------------------------------------------------
-- Turn on ParseOnly so that we don't actually execute anything.
SET PARSEONLY ON 
GO

-- Create a table to iterate through
declare @ObjectList table (ID_NUM int NOT NULL IDENTITY (1, 1), OBJ_NAME varchar(255), OBJ_TYPE char(2))

-- Get a list of most of the scriptable objects in the DB.
insert into @ObjectList (OBJ_NAME, OBJ_TYPE)
SELECT   name, type
FROM     sysobjects WHERE type in ('P', 'FN', 'IF', 'TF', 'TR', 'V')
order by type, name

-- Var to hold the SQL that we will be syntax checking
declare @SQLToCheckSyntaxFor varchar(max)
-- Var to hold the name of the object we are currently checking
declare @ObjectName varchar(255)
-- Var to hold the type of the object we are currently checking
declare @ObjectType char(2)
-- Var to indicate our current location in iterating through the list of objects
declare @IDNum int
-- Var to indicate the max number of objects we need to iterate through
declare @MaxIDNum int
-- Set the inital value and max value
select  @IDNum = Min(ID_NUM), @MaxIDNum = Max(ID_NUM)
from    @ObjectList

-- Begin iteration
while @IDNum <= @MaxIDNum
begin
  -- Load per iteration values here
  select  @ObjectName = OBJ_NAME, @ObjectType = OBJ_TYPE
  from    @ObjectList
  where   ID_NUM = @IDNum 

  -- Get the text of the db Object (ie create script for the sproc)
  SELECT @SQLToCheckSyntaxFor = OBJECT_DEFINITION(OBJECT_ID(@ObjectName, @ObjectType))

  begin try
    -- Run the create script (remember that PARSEONLY has been turned on)
    EXECUTE(@SQLToCheckSyntaxFor)
  end try
  begin catch
    -- See if the object name is the same in the script and the catalog (kind of a special error)
    if (ERROR_PROCEDURE() <> @ObjectName)
    begin
      print 'Error in ' + @ObjectName
      print '  The Name in the script is ' + ERROR_PROCEDURE()+ '. (They don''t match)'
    end
    -- If the error is just that this already exists then  we don't want to report that.
    else if (ERROR_MESSAGE() <> 'There is already an object named ''' + ERROR_PROCEDURE() + ''' in the database.')
    begin
      -- Report the error that we got.
      print 'Error in ' + ERROR_PROCEDURE()
      print '  ERROR TEXT: ' + ERROR_MESSAGE() 
    end
  end catch

  -- Setup to iterate to the next item in the table
  select  @IDNum = case
            when Min(ID_NUM) is NULL then @IDNum + 1
            else Min(ID_NUM)
          end  
  from    @ObjectList
  where   ID_NUM > @IDNum

end
-- Turn the ParseOnly back off.
SET PARSEONLY OFF 
GO

6 回复 | 直到 6 年前

Oleg 15 年前

你可以选择不同的方式。首先是SQL Server 2008 支持依赖项 存在于存储过程的包含db的依赖项中(请参见 http://msdn.microsoft.com/en-us/library/bb677214%28v=SQL.100%29.aspx , http://msdn.microsoft.com/en-us/library/ms345449.aspx 和 http://msdn.microsoft.com/en-us/library/cc879246.aspx )您可以使用sys.sql_expression_dependencies和sys.dm_sql_referenced_entities来查看和验证它们。

但对所有存储过程进行验证的最简单方法是:

导出所有存储过程
删除旧的现有存储过程
导入刚刚导出的存储过程。

如果升级db,则不会验证现有存储过程,但如果创建新存储过程,则会验证该过程。因此,在导出和导出所有存储过程之后,您将收到所有报告的现有错误。

还可以使用以下代码查看和导出存储过程的代码

SELECT definition
FROM sys.sql_modules
WHERE object_id = (OBJECT_ID(N'spMyStoredProcedure'))

更新的 :要查看存储过程SPmyStoredProcedure引用的对象(如表和视图),可以使用以下方法:

SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name 
    ,referenced_server_name AS server_name
    ,referenced_database_name AS database_name
    ,referenced_schema_name AS schema_name
    , referenced_entity_name
FROM sys.sql_expression_dependencies 
WHERE referencing_id = OBJECT_ID(N'spMyStoredProcedure');

更新2 :在我回答的评论中,马丁·史密斯建议使用 sys.sp_refreshsqlmodule 而不是重新创建存储过程。所以有了密码

SELECT 'EXEC sys.sp_refreshsqlmodule ''' + OBJECT_SCHEMA_NAME(object_id) +
              '.' + name + '''' FROM sys.objects WHERE type in (N'P', N'PC')

一个接收一个脚本,该脚本可用于验证存储过程依赖性。输出如下(AdventureWorks2008示例):

EXEC sys.sp_refreshsqlmodule 'dbo.uspGetManagerEmployees'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetWhereUsedProductID'
EXEC sys.sp_refreshsqlmodule 'dbo.uspPrintError'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeeHireInfo'
EXEC sys.sp_refreshsqlmodule 'dbo.uspLogError'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeeLogin'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeePersonalInfo'
EXEC sys.sp_refreshsqlmodule 'dbo.uspSearchCandidateResumes'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetBillOfMaterials'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetEmployeeManagers'

Daniel Hillebrand 10 年前

以下是我的工作:

-- 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

您应该获取所有缺失的SP依赖项,解决后期绑定的问题。

例外 : is_caller_dependent =1不一定意味着依赖关系断开。这仅仅意味着依赖关系在运行时得到解析,因为没有指定被引用对象的架构。可以避免指定被引用对象的模式(例如,另一个SP)。

学分 Jay's blog 匿名评论员…

Keith Adler 15 年前

我喜欢使用显示估计执行计划。它可以合理地突出显示许多错误,而不必真正运行过程。

devio 14 年前

我在以前的项目中遇到了同样的问题,并写了一篇 TSQL checker 在sql2005及更高版本上 Windows program 实现相同的功能。

Michael Sorens 13 年前

当我遇到这个问题时,我很想找到一种安全、无创、快速的技术来验证语法和对象(表、列)引用。

虽然我同意实际执行每个存储过程可能会出现更多的问题,而不仅仅是编译它们,但必须谨慎使用前一种方法。也就是说,您需要知道实际上,执行每个存储过程是安全的(例如,它是否删除了一些表?).这个安全问题可以通过将执行包装在事务中并回滚来解决,这样就不会有永久性的更改,如Devio的回答中所建议的那样。不过,这种方法可能需要相当长的时间,这取决于您操作的数据量。

问题中的代码和Oleg答案的第一部分都建议重新实例化每个存储过程,因为该操作将重新编译该过程并执行此类语法验证。但是这种方法是有侵入性的——对于一个私有的测试系统来说是可以的,但是可能会破坏其他开发人员在一个被大量使用的测试系统上的工作。

我看到了那篇文章 Check Validity of SQL Server Stored Procedures, Views and Functions ,它提供.NET解决方案,但它是 follow-up post 底部的“ddblue”让我更感兴趣。此方法获取每个存储过程的文本,并将 create 关键字到 alter 这样就可以编译它,然后编译该过程。这准确地报告了任何坏的表和列引用。代码运行,但由于创建/更改转换步骤,我很快遇到了一些问题。

从“create”到“alter”的转换将查找由单个空格分隔的“create”和“proc”。在现实世界中,可能有空格或制表符,也可能有一个或多个。我添加了一个嵌套的“替换”序列(感谢 this article 杰夫·摩登!)将所有此类事件转换为单个空间,允许转换按最初设计的方式进行。然后,由于需要在使用原始“sm.definition”表达式的任何位置使用该表达式,因此我添加了一个通用表表达式,以避免大量、不美观的代码重复。下面是我更新的代码版本:

DECLARE @Schema NVARCHAR(100),
    @Name NVARCHAR(100),
    @Type NVARCHAR(100),
    @Definition NVARCHAR(MAX),
    @CheckSQL NVARCHAR(MAX)

DECLARE crRoutines CURSOR FOR
WITH System_CTE ( schema_name, object_name, type_desc, type, definition, orig_definition)
AS -- Define the CTE query.
( SELECT    OBJECT_SCHEMA_NAME(sm.object_id) ,
            OBJECT_NAME(sm.object_id) ,
            o.type_desc ,
            o.type,
            REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(sm.definition, char(9), ' '))), '  ', ' ' + CHAR(7)), CHAR(7) + ' ', ''), CHAR(7), '') [definition],
            sm.definition [orig_definition]
  FROM      sys.sql_modules (NOLOCK) AS sm
            JOIN sys.objects (NOLOCK) AS o ON sm.object_id = o.object_id
  -- add a WHERE clause here as indicated if you want to test on a subset before running the whole list.
  --WHERE     OBJECT_NAME(sm.object_id) LIKE 'xyz%'
)
-- Define the outer query referencing the CTE name.
SELECT  schema_name ,
        object_name ,
        type_desc ,
        CASE WHEN type_desc = 'SQL_STORED_PROCEDURE'
             THEN STUFF(definition, CHARINDEX('CREATE PROC', definition), 11, 'ALTER PROC')
             WHEN type_desc LIKE '%FUNCTION%'
             THEN STUFF(definition, CHARINDEX('CREATE FUNC', definition), 11, 'ALTER FUNC')
             WHEN type = 'VIEW'
             THEN STUFF(definition, CHARINDEX('CREATE VIEW', definition), 11, 'ALTER VIEW')
             WHEN type = 'SQL_TRIGGER'
             THEN STUFF(definition, CHARINDEX('CREATE TRIG', definition), 11, 'ALTER TRIG')
        END
FROM    System_CTE
ORDER BY 1 , 2;

OPEN crRoutines

FETCH NEXT FROM crRoutines INTO @Schema, @Name, @Type, @Definition

WHILE @@FETCH_STATUS = 0 
    BEGIN
        IF LEN(@Definition) > 0
            BEGIN
                -- Uncomment to see every object checked.
                -- RAISERROR ('Checking %s...', 0, 1, @Name) WITH NOWAIT
                BEGIN TRY
                    SET PARSEONLY ON ;
                    EXEC ( @Definition ) ;
                    SET PARSEONLY OFF ;
                END TRY
                BEGIN CATCH
                    PRINT @Type + ': ' + @Schema + '.' + @Name
                    PRINT ERROR_MESSAGE() 
                END CATCH
            END
        ELSE
            BEGIN
                RAISERROR ('Skipping %s...', 0, 1, @Name) WITH NOWAIT
            END
        FETCH NEXT FROM crRoutines INTO @Schema, @Name, @Type, @Definition
    END

CLOSE crRoutines
DEALLOCATE crRoutines

Cá´ÊÊ bcherry 6 年前

在我第一次提出这个问题的九年后,我刚刚发现了一个由微软自己构建的惊人的工具,它不仅能够可靠地验证SQL Server版本之间的存储过程兼容性,而且还能验证所有其他内部方面。它被重新命名了几次,但他们现在称之为:

Microsoft_®数据迁移助手v4.2

https://www.microsoft.com/en-us/download/details.aspx?id=53595

数据迁移助手(DMA)使您能够通过检测可能影响新版本SQL Server上数据库功能的兼容性问题升级到现代数据平台。它为您的目标环境推荐性能和可靠性改进。它不仅允许您将模式和数据,还允许您将不包含的对象从源服务器移动到目标服务器。

以上使用的答案 EXEC sys.sp_refreshsqlmodule 这是一个很好的开始,但在2008 R2上运行它时遇到了一个主要问题:重命名的任何存储过程或函数(使用 sp_rename ,而不是Drop/Create模式)在运行刷新过程后恢复到其先前的定义,因为内部元数据没有在新名称下刷新。这是一个已知的错误,已在SQL Server 2012中修复,但之后我们恢复了一天。(一个解决方法,未来的读者,是在刷新引发错误时发出回滚。)

不管怎样,时代已经改变了,新的工具也出现了——而且是很好的工具——因此,这个答案后来又出现了。