Find stored procedures that won’t compile

Here’s a handy little code snippet.  Find any stored procedures that won’t compile on SQL Server.

The routine will list all procedures, showing ‘– Failed’ with the error messages for those that fail.

Note that recompiling the procedures may also throw dependency errors also that can’t be caught with try/catch block … these errors likely indicate an issue as well.

SET NOCOUNT ON

DECLARE @ProcedureName VARCHAR(2048)
DECLARE @ProcedureBody VARCHAR(MAX)

DECLARE @RoutineName varchar(500)

DECLARE procCursor CURSOR STATIC FORWARD_ONLY READ_ONLY
 FOR
 SELECT
 --TOP 1
 SCHEMA_NAME(schema_id) + '.' + NAME AS ProcedureName,
 OBJECT_DEFINITION(o.[object_id]) AS ProcedureBody
 FROM sys.objects AS o
 WHERE o.[type] = 'P'
 ORDER BY o.[name]

OPEN procCursor
FETCH NEXT FROM procCursor INTO @ProcedureName, @ProcedureBody

WHILE @@FETCH_STATUS = 0
BEGIN
 -- Might have to play with this logic if you don't have discipline in your create statements
 SET @ProcedureBody = REPLACE(@ProcedureBody, 'CREATE PROCEDURE', 'ALTER PROCEDURE')

 BEGIN TRY
   EXECUTE(@ProcedureBody)
   PRINT @ProcedureName + ' -- Succeeded'
 END TRY
 BEGIN CATCH
   PRINT @ProcedureName + ' -- Failed: ' + ERROR_MESSAGE()
 END CATCH

 FETCH NEXT FROM procCursor INTO @ProcedureName, @ProcedureBody
END

CLOSE procCursor
DEALLOCATE procCursor

Advertisements

2 thoughts on “Find stored procedures that won’t compile

  1. Awesome blog! Is your theme custom made or did you download it
    from somewhere? A design like yours with a few simple adjustements would really make my blog jump out.
    Please let me know where you got your design. Thanks a lot

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s