Dependencies View 2008

, 2012-11-30 (first published: )

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

Rate

4 (1)

Share

Share

Rate

4 (1)

Related content

Using SQL Server meta data to list tables that make up views

One of the issues I often face is the need to find views that are already established for certain tables. This maybe for other developers, end users or even for myself. I could search the system tables to find this or explore each view, but are there other ways to easily find a list of all tables that are used for a view or even a list of all views that a table is tied to?

2008-12-12

3,940 reads