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

Answer 2

The check suggested by KenJ is definitely the best one, since the recreate/alter-approaches does not find all errors. E.g.

• impossible execution plans due to query-hints
• I even had an SP referencing a non-existing table that went through without the error being detected.

Please find my version that checks all existing SPs at once with KenJ's method below. AFAIK, it will detect every error that will keep the SP from being executed.

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

Answer 3

If you are using sql 2008 r2 or below then do not use

SET NOEXEC ON

It only checks the syntax and not for potential errors like the existence of tables or columns. Instead use:

SET FMTONLY ON

it will do a full compile as it tries to return the meta data of the stored procedure.

For 2012 and you will need to use stored procedure: sp_describe_first_result_set

Also you can do a complete script in Tsql that checks all sp and views, its just a bit of work.

UPDATE I wrote a complete solution for in tsql that goes through all user defined stored proceedures and checks there syntax. the script is long winded but can be found here http://chocosmith.wordpress.com/2012/12/07/tsql-recompile-all-views-and-stored-proceedures-and-check-for-error/