November 2, 2012 at 3:51 pm
I tinkered with it a bit and came up with this:
It could be better (if I could write it in 2008 or better it'd work with dynamic sql too). But,you can look at a given database and know which procs update which tables, which procs have select * in them and whole lotta other crap. You can join it with sys.comments and find all the places in your database where the code 'plc' for example is referenced and what those dependencies are. Handy.
create view [dbo].[vwDependencies] as
with vw_deps (Name, object_id,parent_id,Type,DependencySchema,DependencyAction,DependencyTable,DependencyColumn,Type2,SelectedFrom,Updates,SelectStarFrom)
as(
select
o.[name],o.object_id,o2.object_id parent_id,
o.type_desc type,
OBJECT_SCHEMA_NAME(d.referenced_major_id) DependencySchema,
o2.name DependencyAction,
IsNull(o2.Name, OBJECT_NAME(d.referenced_major_id)) DependencyTable,
COL_NAME(d.referenced_major_id, d.referenced_minor_id) DependencyColumn,
IsNull(o2.type_desc,'Column') Type2,
d.is_selected SelectedFrom,d.is_updated Updates,d.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_dependencies d on d.object_id = m.object_id
left join sys.sql_modules m2 on m2.object_id = d.referenced_major_id
left join sys.objects o2 on o2.object_id = m2.object_id
where o.object_id <> d.referenced_major_id )
, deps (Name, object_id,parent_id,Type,DependencySchema,DependencyAction,DependencyTable,DependencyColumn,Type2,SelectedFrom,Updates,SelectStarFrom,[Level])
as (
SELECT name,object_id,parent_id,Type,DependencySchema,DependencyAction,DependencyTable,DependencyColumn,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.DependencySchema,d.DependencyAction,d.DependencyTable,d.DependencyColumn,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.DependencySchema,d.DependencyAction,d.DependencyTable,d.DependencyColumn,d.type2,SelectedFrom,Updates,SelectStarFrom,[Level]
FROM deps d
Quick! someone improve it more!!!!
November 2, 2012 at 9:04 pm
Aaaand... the 2008 version
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
November 5, 2012 at 6:44 am
this is really good what you posted
my original idea was to show just the tree of dependencies
no details about columns just the Map so that you could see
who called who and what called you
like
routine 1
<--- is called by Routine 3
<--- is called by routine 4
---> calls table 1
etc..
:O) but yes anyone could use all that info and plug it into it and see even more
have to be carefull if the output gets so complex it might render useless
the original idea to see the Tree of Calls..
December 24, 2012 at 12:26 pm
Nice article.
December 25, 2012 at 8:13 am
Nice one!
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply