Finding Object Dependencies

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

Advertisements

One Response to Finding Object Dependencies

  1. […] follow up on something that was mentioned in the Finding Object Dependencies post, where I observed some oddities with the sys.objects records for user-defined tables types. In […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: