At work, we’re removing some functions from our main application (due to a business reorganization, our core application no longer is supporting certain functionality), which means removing tables that we no longer will use. As part of the cleanup, I wanted a script to help double-check that no procedures were left behind that reference the removed objects.
On one database I ran the script on, some User-Defined table types came up as the Invalid Object, even though they did exist in the database. At first, I assumed that the Table Types weren’t returned by sys.objects, but I did notice a ‘TT’ that represented Table Types as an Object Type. It turns out that those types are returned, but for the object name, a ‘TT_’ is appended to the front of the name, and an eight character Hex code is appended at the end of the name.

select * from sys.objects where type = ‘TT’

So I ended up including some results from sys.types in my sub-query for valid objects.
Another interesting result is that some Update procedures were returned that used this syntax:

update t
set Column = value
from TableName as t

So in this case ‘TableName’ is a valid object, the query returned a result because the Update clause referred to an alias instead of the actual object.
So below is my final query. If you use multiple schemas in your database, or if your procs refer to objects in other databases, then this may not work for you.

— Objects where dependencies don’t exist

select o.name as ObjectName,
o.type_desc as ObjectType,
d.referenced_entity_name as InvalidReferencedObject
from sys.sql_expression_dependencies as d
join sys.objects as o
on o.object_id = d.referencing_id
left join (
select name from sys.types where is_user_defined = 1
union
select name from sys.objects where is_ms_shipped = 0
) as ro
on ro.name = d.referenced_entity_name
where ro.name is null
and d.referenced_entity_name ‘dtproperties’
order by o.name

go