pointers, solutions and scripts for the SQL DBA
not intended to replace common sense

2/21/2012

Find Dependencies

Here's a script from my toolbox I use to find dependencies between SQL objects.  The where clause has a number of sample filters.


--dependencies by dependency
select
ReferencingObjectName = rs.name + '.' + ro.name
, ReferencingObjectType = ro.type_desc
, ReferencedObjectName = s.name + '.' + o.name
, ReferencedObjectType = o.type_desc
from sys.sql_expression_dependencies d
inner join sys.objects o on d.referenced_id = o.object_id or d.referenced_minor_id = o.object_id
inner join sys.schemas s on o.schema_id = s.schema_id
inner join sys.objects ro on d.referencing_id = ro.object_id
inner join sys.schemas rs on ro.schema_id = rs.schema_id
where
ro.is_ms_shipped = 0
and o.is_ms_shipped = 0
--and op.type_desc = 'SQL_STORED_PROCEDURE'
and s.name = 'ObjectSchema'
and o.name = 'ObjectName'
group by rs.name , ro.name, s.name , o.name , ro.type_desc, o.type_desc
order by ro.name, o.name

No comments:

Post a Comment

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.