Syntax check all stored procedures?

Question

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. Enter
2. Alt+C
3. Escape
4. Escape
5. Down Arrow
6. Goto 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):

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

---

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'

Answer 1

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

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:

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