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