pointers, solutions and toolbox scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Tuesday, February 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
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
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: