验证T-SQL存储过程的可靠方法
副标题[/!--empirenews.page--]
我们正在从SQL Server 2005升级到2008.几乎2005实例中的每个数据库都设置为2000兼容模式,但我们跳到2008年.我们的测试已经完成,但我们学到的是我们需要得到的它更快. 我发现了一些存储过程,它们从缺少的表中选择数据或尝试ORDER BY不存在的列. 包装SQL以在SET PARSEONLY ON中创建过程并在try / catch中捕获错误仅捕获ORDER BY中的无效列.在从缺失表中选择数据的过程中找不到错误.然而,SSMS 2008的智能感知会找到问题,但我仍然可以继续并成功运行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 解决方法您可以选择不同的方式.首先,SQL SERVER 2008支持存储在STORED PROCEDURE的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来查看和验证.但是,验证所有STORED PROCEDURE的最简单方法如下: >出口所有存储过程 如果升级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:在对我的回答的评论中,Martin Smith建议使用sys.sp_refreshsqlmodule而不是重新创建存储过程.所以用代码 SELECT 'EXEC sys.sp_refreshsqlmodule ''' + OBJECT_SCHEMA_NAME(object_id) + '.' + name + '''' FROM sys.objects WHERE type in (N'P',N'PC') (编辑:西双版纳站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |