Finding Missing Dependencies

While working with a customer on a SQL port, I came across a system view that was new with SQL Server 2008.  Have you ever needed to check your database code (stored procedures) to see if it references any missing objects?  As of SQL Server 2008 this is easy thanks to:

 sys.sql_expression_dependencies

More can be found here:

sys.sql_expression_dependencies (Transact-SQL)
https://msdn.microsoft.com/en-us/library/bb677315

As with my customer’s case, issues like this can creep into code because of SQL Server’s Deferred Name Resolution Feature.  This simply means SQL Server will let you compile a stored procedure that refers to an object that doesn’t yet exist.  You can read more about this here:

https://msdn.microsoft.com/en-us/library/ms190686(v=sql.105).aspx

In this situation, missing objects – and errors – may not be found until runtime.  Using this system view, we can get an idea of what our exposure is to 208 errors = “Invalid Object Name”.

This example is a simple illustration for a table reference, but you can expand this to other object types as well.  First, we create a stored procedure that references a table that doesn’t exist:

 create procedure DNRproc
as
    -- select from a non-existent table
    select * from dbo.Address
go

Then, we execute:

 exec DNRproc

and get:

 Msg 208, Level 16, State 1, Procedure DNRproc, Line 4
Invalid object name 'dbo.Address'.

To find this missing object reference, we can use:

 select
    object_name(referencing_id) as 'object making reference',
    referenced_class_desc,
    referenced_schema_name,
    referenced_entity_name as 'object name referenced',
    (select object_id from sys.objects where name = [referenced_entity_name]) as 'Object Found?'
from sys.sql_expression_dependencies e
    left join sys.tables t
    on e.referenced_entity_name = t.name

A “NULL” value in the “Object Found?” column indicates the object was not found in sys.objects.  Therefore, we can easily see that dbo.Address which was referenced by DNRproc was not found. 

This doesn’t replace good coding and migration practices, but can be a helpful tool – and can be expanded upon to check for more objects than just missing tables.

-Jay