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

You can use this tool put together at CodeProject which uses built-in SQL functions and options to generate a text file of invalid stored procedures.

http://www.codeproject.com/KB/database/validatingsql.aspx

Your best bet is to write some database unit tests, if you are using VS2008 then it is pretty simple to do, but time-consuming.

But, then you can run your tests and ensure that everything is either working as you expect, or get a list of functions you need to look at.

You may find this article on database unit testing to be interesting, but I do believe it may require VS Team System.:

http://www.developer.com/db/article.php/3758601/Introducing-Visual-Studio-Team-System-2008-Database-Unit-Testing.htm

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;

Create procedure data_tranfer
(@C1 int)
AS
BEGIN
INSERT INTO coindesk
(UPDATED,
UPDATEDISO,
UPDATEDUK,
USDCODE,
USDSYMBOL,
USDRATE,
USDDESCRIPTION,
USDRATE_FLOAT,
GBPCODE,              
GBPSYMBOL,
GBPRATE,           
GBPDESCRIPTION,     
GBPRATE_FLOAT,         
EURCODE,           
EURSYMBOL,            
EURRATE,           
EURDESCRIPTION,      
EURRATE_FLOAT)
SELECT UPDATED,
UPDATEDISO,
UPDATEDUK,
USDCODE,
USDSYMBOL,
USDRATE,
USDDESCRIPTION,
USDRATE_FLOAT,
GBPCODE,              
GBPSYMBOL,
GBPRATE,           
GBPDESCRIPTION,     
GBPRATE_FLOAT,         
EURCODE,           
EURSYMBOL,            
EURRATE,           
EURDESCRIPTION,      
EURRATE_FLOAT
FROM stagcoindesk
WHERE C1 = @C1
END;