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:
More can be found here:
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:
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:
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.