Post reply

Routine Dependency Visualizer

  • October 29, 2012 at 11:54 pm

    #283836

    Comments posted to this topic are about the item Routine Dependency Visualizer

  • October 29, 2012 at 11:57 pm

    #1554000

    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![/I]

    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?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • October 30, 2012 at 3:13 am

    #1554044

    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

  • October 30, 2012 at 4:39 am

    #1554077

    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

  • October 30, 2012 at 6:35 am

    #1554123

    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

  • October 30, 2012 at 7:44 am

    #1554166

    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

  • October 30, 2012 at 7:45 am

    #1554167

    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..

  • October 30, 2012 at 7:49 am

    #1554169

    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

  • October 30, 2012 at 7:59 am

    #1554172

    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.

  • October 30, 2012 at 8:48 am

    #1554199

    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')

  • October 30, 2012 at 11:26 am

    #1554285

    There are several comments about missing dependencies. SQL Server can handle delayed name resolution, which allows you to define a sproc even though the dependencies don't yet exist. When this happens the dependencies are not added to the meta data. You can use the sys.sp_refreshsqlmodule to update the meta data.

    This won't solve the issues about dynamic SQL, and I suspect it does not address remote references.

  • October 30, 2012 at 12:13 pm

    #1554312

    yeah i know i have scripts which refresh all objects

    but like you said it does not work all the time

    is a time issue becuase days later it works

    so :O)

  • October 30, 2012 at 12:16 pm

    #1554316

    thanks all of you who have commeted it on the article

    my hope is it could help some one doing something.

    it helped me a similar script at work from which i got the idea

    of writing this article..

    my point of view is really that MS SQL or any database should have similar functionality

    in place so.

    thanks all..

  • October 30, 2012 at 7:34 pm

    #1554456

    angelrapallo 90775 (10/30/2012)

    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..

    I never really expected that it would handle Dynamic SQL. Just mentioned it. That sounds like it will be a real challenge.

    I think the problem I noted with some VIEWs not showing their descendents may have to do with the fact that I usually schema-qualify my tables when I can. That may be throwing it off as others above have suggested.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • October 30, 2012 at 7:59 pm

    #1554462

    Dynamic sql handling could be done but it will take some doing

    maybe writing an extended procedure or simple writing a class inj c# or C++ and

    call it from the script to parse it and return some info..

    i dont dynamic sql, i have jobs which go check and then created

    views based on some logic or bussiness rules

    '

    for example i have a very big and complicated view which returns

    historicsal data is is really simpler to write dynamic sql but instead i

    created a job that checks for certian things to changed and then the job

    creates the view "yes is dynamic sql" but it makes a view out of it

    and bingo so my code doe snot care it calls it and this way i dont need to embed

    sql into my code

    i think it wold be a nice article my code does something like this

    check todays date and other things it asks Do I need to updated certain view?

    yes and it goes and generates some big complex sql script but wraps it into a

    procedure begin

    dinamic sql

    end

    basically my code updates the views and functions and stuff..

    lets say there is a view whih uses some bussiness rule like

    where cat=big and mouse=small and is rainnig

    then the rule changed to cat=small and is sunny

    my code goes and changes the view or sp and that is all

    if you think about it you dont need dynamic sql alobe it could be put inside a procedure

    as a matter of fact microsoft does this when you call dynammic sql

    ms creates a temp view or sp and then calls it specially it you have arguments

    thanks

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply