SQL Server: Way to syntax check all stored procedures?

Question

i want to ensure that all stored procedures are still syntatically 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:

RefreshAllViews.prc

CREATE PROCEDURE dbo.RefreshAllStatistics AS

EXECUTE sp_msForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN'

Answer 1

I got good feedback from my question here

Answer 2

In addition you might want to consider using Visual Studio Team System 2008 Database Edition which, among other things, does a static verification of all stored procedures in the project on build, thus ensuring that all are consistent with the current schema.

Answer 3

A bit of a drawn-out option:

1. Create a copy of the database (backup and restore). You could do this on the target database, if your confidence level is high.
2. Use SSMS to script out all the stored procedures into a single script file
3. DROP all the procedures
4. Run the script to recreate them. Any that can't be created will error out.

Couple of fussy gotchas in here, such as:

• You want to have the "if proc exists then drop proc GO create proc ... GO" syntax to separte each procedure.
• Nested procedures will fail if they call a proc that has not yet been (re)created. Running the script several times should catch that (since ordering them properly can be a real pain).
• Other and more obscure issues might crop up, so be wary.

To quickly drop 10 or 1000 procedures, run

SELECT 'DROP PROCEDURE ' + schema_name(schema_id) + '.' +  name
 from sys.procedures

select the output, and run it.

This assumes you're doing a very infrequent task. If you have to do this regularly (daily, weekly...), please let us know why!

Answer 4

There is no way to do it from T-SQL, or Enterprise Manager, so i had to write something from client code. i won't post all the code here, but the trick is to:

1) Get a list of all stored procedures

 SELECT ROUTINE_NAME AS StoredProcedureName
 FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE' --as opposed to a function
 ORDER BY ROUTINE_NAME

2) Get the stored procedure create T-SQL:

select
   c.text
from dbo.syscomments c
where c.id = object_id(N'StoredProcedureName')
order by c.number, c.colid
option(robust plan)

3) Run the create statement with NOEXEC on, so that the syntax is checked, but it doesn't actually try to create the stored procedure:

connection("SET NOEXEC ON", ExecuteNoRecords);
connection(StoredProcedureCreateSQL, ExecuteNoRecords);
connection("SET NOEXEC ON", ExecuteNoRecords);

Answer 5

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_XML 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 6

I know this is way old, but I created a slightly different version that actually re-creates all stored procedures, thus throwing errors if they cannot compile. This is something you do not achieve by using the SP_Recompile command.

CREATE PROCEDURE dbo.UTL_ForceSPRecompilation
(
    @Verbose BIT = 0
)
AS
BEGIN

    --Forces all stored procedures to recompile, thereby checking syntax validity.

    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @SPName NVARCHAR(255)           

    DECLARE abc CURSOR FOR
         SELECT NAME, OBJECT_DEFINITION(o.[object_id])
         FROM sys.objects AS o 
         WHERE o.[type] = 'P'
         ORDER BY o.[name]

    OPEN abc

    FETCH NEXT FROM abc
    INTO @SPName, @SQL
    WHILE @@FETCH_STATUS = 0 
    BEGIN       

        --This changes "CREATE PROCEDURE" to "ALTER PROCEDURE"
        SET @SQL = 'ALTER ' + RIGHT(@SQL, LEN(@SQL) - (CHARINDEX('CREATE', @SQL) + 6))

        IF @Verbose <> 0 PRINT @SPName

        EXEC(@SQL)

        FETCH NEXT FROM abc
        INTO @SPName, @SQL
    END
    CLOSE abc
    DEALLOCATE abc  

END