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
2 thoughts on “Find stored procedures that won’t compile”
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
I am using the INove theme with some customizations of my own…