语法检查所有存储过程? [英] Syntax check all stored procedures?

问题描述

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

i want to ensure that all stored procedures are still syntactically valid. (This can happen if someone renames/deletes a table/column).

现在我检查所有存储过程语法的解决方案是进入企业管理器，选择列表中的第一个存储过程，并使用该过程:

Right now my solution to check the syntax of all stored procedures is to go into Enterprise Manager, select the first stored procedure in the list, and use the procedure:

1. 输入
2. Alt+C
3. 逃离
4. 逃离
5. 向下箭头
6. 转到 1

它有效，但很乏味.我想要一个名为

It works, but it's pretty tedious. i'd like a stored procedure called

SyntaxCheckAllStoredProcedures

就像我写的另一个存储过程一样，对视图做同样的事情:

like the other stored procedure i wrote that does the same thing for views:

刷新所有视图

为了大家的利益，RefreshAllViews:

For everyone's benefit, 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

<小时>

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

---

For everyone's benefit, a stored procedure to mark all stored procedure as needing a recompile (marking a stored procedure for recompile will not tell you if it's syntactically valid):

重新编译AllStoredProcedures.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 过程.这将通过执行完整数据扫描来更新数据库中的所有统计信息:

---

For completeness sake, the UpdateAllStatistics procedure. This will update all statistics in the database by doing a full data scan:

RefreshAllStatistics.prc

CREATE PROCEDURE dbo.RefreshAllStatistics AS

EXECUTE sp_msForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN'

推荐答案

您也可以就地"执行此操作 - 无需获取所有 create 语句.

You can also do this "in-place" - without getting all the create statements.

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

In addition to setting NOEXEC ON, you will also need to set your favorite SHOWPLAN_* ON (I use SHOWPLAN_TEXT). Now you can get rid of your step 2 and just execute each procedure you retrieved in step 1.

这是一个使用单个存储过程的示例.您可以将其添加到您最喜欢的循环中:

Here is a sample using an individual stored procedure. You can work it into your favorite loop:

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

上述示例应生成以下输出:

The above sample should generate the following output:

消息 208，级别 16，状态 1，程序测试，第 2 行
无效的对象名称missing_table_or_view".

Msg 208, Level 16, State 1, Procedure tests, Line 2
Invalid object name 'missing_table_or_view'.

这篇关于语法检查所有存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！