SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Routine Dependency Visualizer


Routine Dependency Visualizer

Author
Message
angelrapallo 90775
angelrapallo 90775
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 47
Comments posted to this topic are about the item Routine Dependency Visualizer
dwain.c
dwain.c
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28239 Visits: 6431
Angel,

This is indeed a pretty cool script.

I ran it against a small database of mine, after changing '[msdb]' to '[My database].' Aside from some of the information printed still saying "msdb" because I didn't change that string, the information looked pretty darn accurate.

Thanks for this!

Two caveats though.
1. It so happens that the DB I ran it against makes extensive use of Dynamic SQL because it goes after information in other databases. Those references didn't show themselves.
2. Some of the views weren't showing the tables they were pointing at. Is that by design or some quirk of the dependencies lookup you're doing?


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
werner.broser-775735
werner.broser-775735
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 36
nice solution yes,

but not only objects in dynamic sql are ignored, also simple called routines from different databases like
"OtherDB.dbo.usp_whatever" . Of course this would require to parse the t-sql...

regards
Werner
thierry.bachmann.2
thierry.bachmann.2
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 8
Hi,

Very nice thank !

I had already used sys.sql_dependencies but i had found a big problem.

It seems that if stored procedure is created before table then sys.sql_dependencies is not updated by SQL Server.

Have you see this issue ?
Have you a work around ?

Thank
Best regards
Megistal
Megistal
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3849 Visits: 2570
Interesting script thank you.

However there's two things that came out very quickly.

1- The script's does not take into account the schema which is a drawback in our environment because it make it more tedious to find the reported object.
2- Table value functions aren't handled which lead to "hole" in the result list. (I've included a screenshot)

but as always if that script satisfies your needs first, then it's intended goal is achieve!

GJ
Attachments
MissingTVF.jpg (36 views, 75.00 KB)
angelrapallo 90775
angelrapallo 90775
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 47
As it happens i notice thast some objects did not show, i did a deep study and came so what was happenning
for some reason Microsoft was not updating some objects dependencoes so :O(

yes Dynamic SQL is not consider, i thought abnout it but, maybe next revision
I use dynammic SQL but try to to use the least posible, my technique for which i may post and article
is to write Jobs which create objects for all dynamic SQL for example if i need some dynamic SQL
select * frrom table, i write a job that based on some time and other cisrcumstances creates that procedure or view

thanks for the review
angelrapallo 90775
angelrapallo 90775
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 47
yes i have seen it yeah hey is not perfect
i could handle dynamic maybe in the future, becuase i use little dynamic, i use a different technique
to avoid dynamic then..
angelrapallo 90775
angelrapallo 90775
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 47
i had not seen this problem, i ran the script againts 4 or 5 databases including my big ones at work
and all was fine for a few 2 o3 views not showing dependencies, for which i checked directly into the tables and
found the script was reporting correct information, Microsoft was not updating the dependencies
for those views Why?? i have no idea
Megistal
Megistal
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3849 Visits: 2570
Angel,

If I remember correctly (from experience only I don't have anything to back this up) sql_dependencies isn't able to report all dependencies (flaw or by design?).

SSMS use this for the dependency feature and it's not accurate (for SS2k8 R2 RTM) on our side.
The missing objects aren't deferred one and aren't inside dynamic SQL either. Just some plain objects.

The only way I as able to get the job done was searching using object_definition.
Crazy Horse
Crazy Horse
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 6
Angel, thank you for the idea but I would rather use the following script instead (SQL2008 and up):
SELECT DISTINCT referenced_entity_name FROM sys.dm_sql_referenced_entities ('[schema].[objectname]', 'OBJECT')
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search