Check if Stored procedures have syntax errors

I have tons of stored procedures in my database. We are constantly changing the data structure (we are in development) Is there a tool that will tell me which stored procedures won’t compile?

When you create a stored procedure it prevents you if there is an invalid table or column, but if you change the column name after the stored procedure is created, your proc is invalid. I’d like to get a list of those invalid procs.

cheers!

Joseph

Answer

A considerable improvement to monitor is to have a Continuous Integration (CI) that checks every so often (it can be every two hours or daily) the objects in the database. Another option is to create a windows service that invokes an endpoint.

CI or windows service can send notifications to the development team when someone broke the build.

In the endpoint of that backend, you can have a procedure like this that will inform you about the views, stored procedures, and functions with errors in your database.

CREATE PROCEDURE Get_Objects_With_Errors 
AS

    SET NOCOUNT ON;
    DECLARE @objectName AS VARCHAR(255);

    DECLARE @count int = 0;

    -- Find all views in dbo schema
    DECLARE listViews CURSOR FOR
        SELECT [TABLE_NAME]
        FROM INFORMATION_SCHEMA.VIEWS v
        WHERE [TABLE_SCHEMA] = 'dbo';

    OPEN listViews
    FETCH NEXT FROM listViews into @objectName;

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
        BEGIN TRY
            EXEC sp_refreshview @objectName;
        END TRY
        BEGIN CATCH
            PRINT @objectName + ' has ERRORS : ' + ERROR_MESSAGE();
            SET @count = @count + 1;
        END CATCH
        FETCH NEXT FROM listViews INTO @objectName;
    END

    CLOSE listViews;
    DEALLOCATE listViews;

    -- Find all procedures and functions in dbo schema
    DECLARE listRoutines CURSOR FOR
        SELECT SPECIFIC_NAME
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_SCHEMA = 'dbo' 
        GROUP BY SPECIFIC_NAME;

    OPEN listRoutines
    FETCH NEXT FROM listRoutines into @objectName;

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
        BEGIN TRY
            EXEC sp_refreshsqlmodule @objectName;
        END TRY
        BEGIN CATCH
            PRINT @objectName + ' has ERRORS : ' + ERROR_MESSAGE();
            SET @count = @count + 1;
        END CATCH
        FETCH NEXT FROM listRoutines INTO @objectName;
    END

    CLOSE listRoutines;
    DEALLOCATE listRoutines;

    PRINT 'Total with errors : ' + CAST(@count AS nvarchar(10));

SET NOCOUNT OFF;