Fastest way to find DB object dependencies

  • ksatpute123

    Hall of Fame

    Points: 3325

    Comments posted to this topic are about the item Fastest way to find DB object dependencies

  • janis.l.murphy

    SSC Veteran

    Points: 239

    The solution was tidy, but I'm not sure calling the referenced object a "child" of the dbobject makes much sense. Also, I found another limitation (and one I cannot offer a fix for): Sometimes not all the dependencies for views come through. I have a view with multiple CTEs. In one environment 5 of 6 references were found. In another, a different set of 5 of the 6 references were found. I cannot fathom why that would happen.

  • ksatpute123

    Hall of Fame

    Points: 3325

    Sorry for the late reply. Thanks for your feedback.

    Calling the referenced object a "child" of the dbobject was something of requirement at my end. I assumed people using the code may change it as per their needs, so I kept it as it is.

    I couldn't recreate the scenario you pointed out. It would be helpful if you could share more details about it so the issue can be tackled.

  • janis.l.murphy

    SSC Veteran

    Points: 239

    I can't think of a good way to produce an example of the issue I found. Just imagine that your view has more than one CTE and each CTE has different references. In my database, I found that only some of the references were logged in the sys.sql_dependencies table. Oddly enough, in other environments the same thing happened, but a different subset of referenced objects were logged. Perhaps it had something to do with changes to the referenced objects. Perhaps the view just needed to be re-compiled in order that the dependencies be updated. The problem is (if I have to re-compile everything) that wouldn't be feasible, I think.

  • SQL Refactor Studio Team

    SSC Rookie

    Points: 37

    You can try a free addin for SQL Server Management Studio - SQL Refactor Studio (http://sqlrefactorstudio.com/)

    Key Features:

    - Advanced Search dependences of object (databases, files on disk, query editor)

    - Advanced Search dependencies free text (in databases, files on disk, query editor)

    - Refactoring "Rename"

    - Refactoring "Add CRUD Methods"

    - Refactoring "Move columns"

    - Refactoring "Add lookup table"

    - Formatting SQL code

    - And many other useful features!

    ______________________________
    Sincerely,
    SQL Refactor Studio Team
    http://sqlrefactorstudio.com/

  • mxy

    SSCarpal Tunnel

    Points: 4015

    will this work on SQL 2K5 instance ?

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks. I changed the column names to be a bit more friendly to me.

Viewing 7 posts - 1 through 7 (of 7 total)

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