首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >是否检查所有存储过程的语法?

是否检查所有存储过程的语法?
EN

Stack Overflow用户
提问于 2009-07-24 13:27:14
回答 9查看 26.9K关注 0票数 18

我希望确保所有存储过程在语法上仍然有效。(如果有人重命名/删除表/列,就会发生这种情况)。

现在,要检查所有存储过程的语法,我的解决方案是进入Enterprise Manager,选择列表中的第一个存储过程,然后使用过程:

  1. Enter
  2. Alt+C
  3. Escape
  4. Escape
  5. Down箭头
  6. Goto 1

它是有效的,但相当单调乏味。我想要一个名为

SyntaxCheckAllStoredProcedures

与我编写的其他存储过程一样,它对视图也做同样的事情:

RefreshAllViews

为了每个人的利益,RefreshAllViews:

RefreshAllViews.prc

CREATE PROCEDURE dbo.RefreshAllViews AS

-- This sp will refresh all views in the catalog. 
--     It enumerates all views, and runs sp_refreshview for each of them

DECLARE abc CURSOR FOR
     SELECT TABLE_NAME AS ViewName
     FROM INFORMATION_SCHEMA.VIEWS
OPEN abc

DECLARE @ViewName varchar(128)

-- Build select string
DECLARE @SQLString nvarchar(2048)

FETCH NEXT FROM abc 
INTO @ViewName
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @SQLString = 'EXECUTE sp_RefreshView '+@ViewName
    PRINT @SQLString
    EXECUTE sp_ExecuteSQL @SQLString

    FETCH NEXT FROM abc
    INTO @ViewName
END
CLOSE abc
DEALLOCATE abc

为了每个人的利益,将所有存储过程标记为需要重新编译的存储过程(将存储过程标记为重新编译不会告诉您它在语法上是否有效):

RecompileAllStoredProcedures.prc

CREATE PROCEDURE dbo.RecompileAllStoredProcedures AS

DECLARE abc CURSOR FOR
     SELECT ROUTINE_NAME
     FROM INFORMATION_SCHEMA.routines
    WHERE ROUTINE_TYPE = 'PROCEDURE'
OPEN abc

DECLARE @RoutineName varchar(128)

-- Build select string once 
DECLARE @SQLString nvarchar(2048)

FETCH NEXT FROM abc 
INTO @RoutineName
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @SQLString = 'EXECUTE sp_recompile '+@RoutineName
    PRINT @SQLString
    EXECUTE sp_ExecuteSQL @SQLString

    FETCH NEXT FROM abc
    INTO @RoutineName
END
CLOSE abc
DEALLOCATE abc

为了完整起见,我们使用UpdateAllStatistics过程。这将通过执行完整数据扫描来更新数据库中的所有统计信息:

RefreshAllStatistics.prc

CREATE PROCEDURE dbo.RefreshAllStatistics AS

EXECUTE sp_msForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN'
EN

回答 9

Stack Overflow用户

发布于 2010-01-16 06:16:48

您也可以“就地”执行此操作,而无需获取所有的create语句。

除了设置NOEXEC ON之外,您还需要设置您喜欢的SHOWPLAN_* ON (我使用SHOWPLAN_TEXT)。现在,您可以摆脱步骤2,只执行在步骤1中检索到的每个过程。

下面是一个使用单个存储过程的示例。你可以把它放入你最喜欢的循环中:

create procedure tests @bob int as 
select * from missing_table_or_view
go 

set showplan_text on; 
go 

set noexec on 

exec tests 

set noexec off
go 
set showplan_text off; 
go 
drop procedure tests 
go

上面的示例应该生成以下输出:

消息208,级别16,状态1,程序测试,第2行

无效的对象名‘missing_table_or_view’。

票数 7
EN

Stack Overflow用户

发布于 2012-12-07 18:52:45

如果您使用的是sql 2008 r2或更低版本,则不要使用

将NOEXEC设置为ON

它只检查语法,而不检查潜在的错误,如表或列的存在。请改用:

设置FMTONLY为ON

当它试图返回存储过程的元数据时,它将执行完全编译。

对于2012年,您将需要使用存储过程: sp_describe_first_result_set

你也可以用Tsql做一个完整的脚本来检查所有的sp和视图,这只是一点工作。

更新:我为tsql写了一个完整的解决方案,它检查所有用户定义的存储过程并检查其中的语法。该脚本是冗长的,但可以在http://chocosmith.wordpress.com/2012/12/07/tsql-recompile-all-views-and-stored-proceedures-and-check-for-error/中找到

票数 3
EN

Stack Overflow用户

发布于 2015-09-15 14:55:31

KenJ建议的检查绝对是最好的检查,因为重新创建/更改方法并不能发现所有错误。例如。

由于查询提示,

  • 不可能执行计划
  • 我甚至有一个SP引用了一个不存在的表,但没有检测到错误。

请找到我的版本,使用下面KenJ的方法一次检查所有现有的SP。AFAIK,它将检测将阻止SP执行的每个错误。

--Forces the creation of execution-plans for all sps.
--To achieve this, a temporary SP is created that calls all existing SPs.
--It seems like the simulation of the parameters is not necessary. That makes things a lot easier.
DECLARE @stmt NVARCHAR(MAX) = 'CREATE PROCEDURE pTempCompileTest AS ' + CHAR(13) + CHAR(10)
SELECT @stmt = @stmt + 'EXEC [' + schemas.name + '].[' + procedures.name + '];'
    FROM sys.procedures
        INNER JOIN sys.schemas ON schemas.schema_id = procedures.schema_id
    WHERE schemas.name = 'dbo'
    ORDER BY procedures.name

EXEC sp_executesql @stmt
GO

--Here, the real magic happens.
--In order to display as many errors as possible, XACT_ABORT is turned off.
--Unfortunately, for some errors, the execution stops anyway.
SET XACT_ABORT OFF
GO
--Showplan disables the actual execution, but forces t-sql to create execution-plans for every statement.
--This is the core of the whole thing!
SET SHOWPLAN_ALL ON
GO
--You cannot use dynamic SQL in here, since sp_executesql will not be executed, but only show the string passed in in the execution-plan
EXEC pTempCompileTest
GO
SET SHOWPLAN_ALL OFF
GO
SET XACT_ABORT ON
GO
--drop temp sp again
DROP PROCEDURE pTempCompileTest
--If you have any errors in the messages-window now, you should fix these...
票数 3
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/1177659

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档