Needed a quick way to identify referenced objects that are not contained in my current database either to having being dropped, located in a different database, or located on a linked server.
DevioBlog has a good concise query.
A write-up is available here.
And, here it is in it’s entirety.
select o.name, d.referenced_entity_name, * from sys.sql_expression_dependencies d inner join sys.objects o on d.referencing_id = o.object_id where referenced_id is null
/* sys.sql_expression_dependencies (Transact-SQL) v2008 https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-expression-dependencies-transact-sql?view=sql-server-2017 ID of the referenced entity. a) The value of this column is never NULL for schema-bound references. b) The value of this column is always NULL for cross-server and cross-database references. c) NULL for references within the database if the ID cannot be determined. Objects dropped d) For non-schema-bound references, the ID cannot be resolved in the following cases: The referenced entity does not exist in the database. */ select [object] = quoteName ( isNull ( tblSS.[name] , '' ) ) + '.' + quoteName ( isNull ( tblSO.name , '' ) ) , [objectType] = tblSO.[type_desc] , [referencedDatabase] = tblSED.referenced_database_name , [referencedObject] = quoteName ( isNull ( tblSED.referenced_schema_name , '' ) ) + '.' + quoteName ( isNull(tblSED.referenced_entity_name, '') ) , [classofReferencedObject] = tblSED.referenced_class_desc from sys.objects tblSO inner join sys.schemas tblSS on tblSO.schema_id = tblSS.schema_id inner join sys.sql_expression_dependencies tblSED on tblSO.object_id = tblSED.referencing_id where ( ( ( tblSED.[referenced_id] is null ) ) )
Thank goodness information about referenced objects are cataloged in the sys.sql_expression_dependencies system table.
If the referenced object is not in the contextual database, the referenced_id is logged as null.
Also as part of clean-up effort the referenced_id column is nulled out whenever an object is dropped.