Validating that all Stored procedures are valid

Question

Background
My application is backed up by an SQL Server (2008 R2), and have quite a few SP, triggers etc..
My goal is to make sure upon program start that all of those objects are still valid.
For example, if I have a stored procedure A which calls stored procedure B, If someone changes the the name of B to C, I would like to get a notification when running my application in Debug environment.

What have I tried?
So, I figured using sp_refreshsqlmodule which according to the documentation returns 0 (success) or a nonzero number (failure):

DECLARE @RESULT int 
exec @RESULT  = sp_refreshsqlmodule N'A' --In this case A is the SP name
SELECT @@ERROR
SELECT @RESULT 

So I changed SP B name to C and ran the script. The results where:

• @@ERROR was 0
• @RESULT was 0
• I got a message of:

The module 'A' depends on the missing object 'B'. The module will still be created; however, it cannot run successfully until the object exists.

My question:
Am I missing something here, shouldn't I get anon-zero number that indicates that something went wrong?

Answer 1

Assuming that all of your dependencies are at least schema qualified, it seems like you could use sys.sql_expression_dependencies. For instance, running this script:

create proc dbo.B
as
go
create proc dbo.A
as
exec dbo.B
go
select OBJECT_SCHEMA_NAME(referencing_id),OBJECT_NAME(referencing_id),
   referenced_schema_name,referenced_entity_name,referenced_id
from sys.sql_expression_dependencies
go
sp_rename 'dbo.B','C','OBJECT'
go
select OBJECT_SCHEMA_NAME(referencing_id),OBJECT_NAME(referencing_id),
   referenced_schema_name,referenced_entity_name,referenced_id
from sys.sql_expression_dependencies

The first query of sql_expression_dependencies shows the dependency as:

(No Column name) (No Column name) referenced_schema_name referenced_entity_name referenced_id
dbo              A                dbo                    B                      367340373

And after the rename, the second query reveals:

(No Column name) (No Column name) referenced_schema_name referenced_entity_name referenced_id
dbo              A                dbo                    B                      NULL

That is, the referenced_id is NULL.

---

So this query may find all of your broken stored procedures (or other objects that can contain references):

select OBJECT_SCHEMA_NAME(referencing_id),OBJECT_NAME(referencing_id)
from
    sys.sql_expression_dependencies
group by
    referencing_id
having SUM(CASE WHEN referenced_id IS NULL THEN 1 ELSE 0 END) > 0

Answer 2

You can try this. It may not be 100% for schema (it has owner name below) as it was based more when I worked with SQL Server 2000, but I tested it 2008, it basically runs alter statement on all the procs, functions, views. Comment out the PRINT @objName + ' seems valid.' to see only invalid procs, functions, views... Feel free to edit any parts you want!

DECLARE @objId INT
DECLARE @objName NVARCHAR(max)
DECLARE @owner NVARCHAR(255)
DECLARE @Def nvarchar(max)

DECLARE checker CURSOR FAST_FORWARD FOR
    SELECT
        id, name, USER_NAME(o.uid) owner 
    FROM sysobjects o 
    WHERE   o.type IN ('P', 'TR', 'V', 'TF', 'FN', 'IF')
            AND o.name <> 'RecompileSQLCode'

OPEN checker
FETCH FROM checker INTO @objId, @objName, @owner

WHILE @@FETCH_STATUS=0
BEGIN
    SELECT @Def = definition      
    FROM sys.sql_modules 
    WHERE object_id = @objId


       --print @objName
       --print @def
       SET @def = REPLACE(@def, 'create procedure','alter procedure')
       SET @def = REPLACE(@def, 'create PROC','alter PROC')
       SET @def = REPLACE(@def, 'create trigger','alter trigger')
       SET @def = REPLACE(@def, 'create function','alter function')
       SET @def = REPLACE(@def, 'create view','alter view')
    BEGIN TRANSACTION
        BEGIN TRY
            EXEC sp_executesql @def
            PRINT @objName + ' seems valid.'
        END TRY
        BEGIN CATCH
                PRINT 'Error: ' + @objName + ' : ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + ' ' + ERROR_MESSAGE()
        END CATCH
    ROLLBACK

    FETCH NEXT FROM checker INTO @objId, @objName, @owner
END

CLOSE checker
DEALLOCATE checker

Answer 3

Here is a procedure that will script all of the stored procedures on your server as CREATE procedures with a Suffix to the name. The script created a corresponding DROP PROCEDURE for the 'TEMP/Test' procedure.

This will not confirm if a stored proc is referencing an invalid table name, as the normal create of a stored proc does not validate for this.

BEGIN TRAN

--Creating temp able with copy of all procedures
 DECLARE @tTempProcedures TABLE
 (
    ProcedureName NVARCHAR(MAX),
    OriginalProcCreateSQL NVARCHAR(MAX),
    CreateNewProcSQL NVARCHAR(MAX),
    DropTestProcedureSQL NVARCHAR(MAX),
    AllInOneSQL NVARCHAR(MAX)
 )

 INSERT INTO @tTempProcedures
 SELECT 
         procedures.name                                AS  ProcedureName         
        ,syscomments.Text                               AS  OriginalProcCreateSQL
        ,REPLACE(syscomments.Text
                ,procedures.name
                ,procedures.name + '_TEST_CREATE')
            + ' GO'                                     AS  CreateNewProcSQL
        ,'DROP PROCEDURE ' 
            + procedures.name 
            + '_TEST_CREATE'                         AS  DropTestProcedureSQL

    ,'EXEC sp_executesql ' +''''''+
        REPLACE(
            REPLACE(syscomments.Text
                    ,procedures.name
                    ,procedures.name + '_TEST_CREATE')
                ,''''
                ,'''''')

        +''''''
        +  CHAR(10) + CHAR(13) 

        +  CHAR(10) + CHAR(13) 
        + 'EXEC sp_executesql ' +''''''+ 'DROP PROCEDURE ' 
        +  procedures.name 
        +  '_TEST_CREATE' +''''''
        +  CHAR(10) + CHAR(13)                  
                                                AS  AllInOneSQL
FROM
    syscomments

    Inner Join sys.procedures
    ON syscomments.id = procedures.OBJECT_ID






DECLARE cur CURSOR FOR 
SELECT AllInOneSQL FROM @tTempProcedures 

OPEN cur
DECLARE @AllInOneSQL NVARCHAR(MAX)
FETCH NEXT FROM cur INTO @AllInOneSQL

WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT(@AllInOneSQL)
    EXEC sp_executesql @AllInOneSQL

    FETCH NEXT FROM cur INTO @AllInOneSQL
END

CLOSE cur
DEALLOCATE cur


ROLLBACK

WARNING: Please be careful using any DROP PROCEDURE Statements.

NOTE: You can also use : "SET NOEXEC ON" and then execute the procedure. If the procedure is invalid you will get errors. If the procedure is valid no records will be updated after setting "SET NOEXEC ON". This is however difficult to automate as you need to call the proc with valid parameters.

Answer 4

Strange indeed, I've tried myself and result of sp_refreshsqlmodule is not consistent. Even strange is a transaction remains open in case an error appears, that's I've added ROLLBACK TRAN. Here it is an alternative:

DECLARE
    @is_refresh_ok AS BIT = 0
    , @error_message VARCHAR(MAX)

BEGIN TRY       
    EXEC sp_refreshsqlmodule '<SP name here>'       
    SET @is_refresh_ok = 1
END TRY
BEGIN CATCH
    SET @error_message = ERROR_MESSAGE()

    IF @@TRANCOUNT > 0
    ROLLBACK TRAN
END CATCH   

SELECT @is_refresh_ok, @error_message

In case you need, here it is a script that automatically refresh all stored procedures and functions in a database.

Answer 5

The text of the SP is parsed when the CREATE PROCEDURE statement is executed but external name resolution is deferred until run-time. This allows for, say, circular dependencies between objects and avoids the necessity of having release scripts structured just so. Here's a technet link on the subject. I could see how sp_refreshsqlmodule could re-parse the text of the SP and extract its meta data successfully, reporting 0, but still not bind it to the dependent objects. This is another SO question dealing with the topic.

I've had some success with SQL parsers (SO questions here and here) in other situations. You may be able to capture the EXEC statements and list associated SP name.

Answer 6

SELECT
  OBJECT_NAME(referencing_id) AS [this sproc or view...],
  referenced_entity_name AS [... depends on this missing entity name]
FROM sys.sql_expression_dependencies
WHERE is_ambiguous = 0
  AND OBJECT_ID(referenced_entity_name) IS NULL
  AND referenced_entity_name NOT IN
    (SELECT Name FROM sys.types WHERE is_user_defined = 1)
ORDER BY OBJECT_NAME(referencing_id), referenced_entity_name

I hope this helps. -Thomas