Dependencies View 2008
Example 1
Select * from vwDependencies where name = 'procname'
finds all tables, routines, triggers invoked by procname
Example 2
select * from vwDependencies v inner join
sys.syscomments c on v.name = object_name(c)
where c.text = '['''']code[''']'
find all stored procedures that have 'code' embedded in them and display all the dependencies thereof. Useful for finding hardcoded values that could otentiall be inserted into more than one table.
Example 3
select * from vwDependencies v inner join
sys.syscomments c on v.name = object_name(c)
where c.text = '['''']code[''']' and updates = 1
find the tables updated in Example 2.
Thanks to Angel Rappalo who published the Routine Dependency Visualizer upon which this work is based.
alter view [dbo].[vwDependencies2008] as
with vw_deps (Name, object_id,parent_id,Type,DepServer,DepSchema,DepAction,DepTable,DepColumn,Type2,SelectedFrom,Updates,SelectStarFrom)
as(
select
o.[name],o.object_id,o2.object_id parent_id,
o.type_desc type,
d.referenced_server_name DepServer,
d.referenced_schema_name DepSchema,
o2.name DepAction,
case when o2.name is null then d.referenced_entity_name end DepTable,
COL_NAME(d2.referenced_major_id, d2.referenced_minor_id) DependencyColumn,
IsNull(o2.type_desc,'Column') Type2,
d2.is_selected SelectedFrom,d2.is_updated Updates,d2.is_select_all SelectStarFrom
from
sys.objects o
inner join sys.sql_modules m on o.object_id = m.object_id
left join sys.sql_expression_dependencies d on d.referencing_id = m.object_id
left join sys.sql_dependencies d2 on d2.object_id = m.object_id
left join sys.sql_modules m2 on m2.object_id = d.referenced_id
left join sys.objects o2 on o2.object_id = m2.object_id
where o.object_id <> d.referenced_id )
, deps (Name, object_id,parent_id,Type,DepServer,DepSchema,DepAction,DepTable,DepColumn,Type2,SelectedFrom,Updates,SelectStarFrom,[Level])
as (
SELECT name,object_id,parent_id,Type,DepServer,DepSchema,DepAction,DepTable,DepColumn,type2,SelectedFrom,Updates,SelectStarFrom,0 AS [Level]
FROM vw_deps d where parent_id is null
UNION ALL
--Recursive member definition
SELECT d.name,d.object_id,d.parent_id,d.Type,d.DepServer,d.DepSchema,d.DepAction,d.DepTable,d.DepColumn,d.type2,dp.SelectedFrom,dp.Updates,dp.SelectStarFrom,Level+1 from
vw_deps d
inner JOIN deps AS dp
ON d.parent_id = dp.object_id
)
-- Statement that executes the CTE
SELECT d.name,d.object_id,d.parent_id,d.Type,d.DepServer,d.DepSchema,d.DepAction,d.DepTable,d.DepColumn,d.type2,SelectedFrom,Updates,SelectStarFrom,[Level]
FROM deps d
