Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: SP with list of tables and dependency depth...

    I'm having a brain freeze.

    I seem to recall that there is a system stored procedure that lists table objects and the "depth" of their dependencies. Something like:

    Code:
    table name          3
    table 2 name       3
    table 3 name       2
    table 4 name       1
    table 5 name       0
    The results show the name of the table object and the numerical depth of dependencies (ie, if the table had no foreign keys, the depth would be 0).

    Does this ring a bell with anyone? I've googled, but my google-shui is weak today.

    Regards,

    hmscott
    Last edited by hmscott; 07-24-06 at 11:06.
    Have you hugged your backup today?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Never heard of it. You could write your own code to plumb dependencies between tables based upon the SCHEMA tables or the system tables. How would you count the depth of circular dependencies, though? And why is depth important anyway?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It sounds vaguely like @@nestlevel, but that is used for how deeply you have nested stored procedures.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by blindman
    And why is depth important anyway?
    I found out (the hard way) with replication that the order in which you add tables to the publication is critical when dealing with dependencies. I have a publication with 160+ tables, so I wanted to script it all out (instead of using the GUI to build it).

    So far, it has been a very tiresome process, but educational as well.

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Okay, maybe I had one too many margaritas...I coulda sworn there was an MS system sp to do this, but I did not find it. I did some additional searching on the 'Net and I think I found what I was looking for:

    http://www.sqlservercentral.com/scri...p?scriptid=759

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •