Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2011
    Posts
    3

    Question Unanswered: Query to identifying parent and child tables

    I'm looking for a query (sql server 2005 and up) that given a TableName it returns me its parents.

    something like...

    Select parent_table from "???Table???"
    Where childTableName = "MyChildTableName"

    Can I do that?

    Even better if I could have a query that gets me recursivilly the parent table.
    such as...
    [Client] 1-* [Order] 1-* [Items]

    Select parent_table, child_table from "???Table???"
    Where childTableName = "Items"

    parent_table | child_table
    --------------------------
    Client | Order
    Order | Items

    it goes all the way up until it finds Client, that has no parent...

    Is it possible?

    T.I.A.
    Monica

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How do you define "parent-ness" ??? In other words, do you use indicies, DRI (Declarative Referential Integrity), a tool with an external data dictionary (like PeopleSoft), or something completely different?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2011
    Posts
    3
    Maybe I wasn't clear...
    let's say I have the following:

    TableClient
    ID PK
    Name

    TableOrder
    ID PK
    ClientID FK_Client
    Reason

    TableItems
    ID PK
    OrderID FK_Order
    ItemName

    TableOrder has a Client_FK, so TableClient is TableOrder parent
    same with TableItems, it has an FK_Order, then TableOrder is Parent of TableItems.
    I need a query to have this result. Using a tool would not solve my problem


    Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by maraujo4 View Post
    Maybe I wasn't clear...
    let's say I have the following:
    if you have those tables, and you know you have those tables, and you even know what the PKs and FKs are, why do you need a query to tell you that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2011
    Posts
    3

    Wink

    no. I don't know that I have these tables... I'm working on a flexible database where user can create new tables, change fields and so on...
    anyway, I found the query I was looking for...
    If anyone's interested, here is the query

    Select

    object_name(rkeyid) Parent_Table,

    object_name(fkeyid) Child_Table,

    object_name(constid) FKey_Name,

    c1.name FKey_Col,

    c2.name Ref_KeyCol

    From

    sys.sysforeignkeys s

    Inner join sys.syscolumns c1

    on ( s.fkeyid = c1.id And s.fkey = c1.colid )

    Inner join syscolumns c2

    on ( s.rkeyid = c2.id And s.rkey = c2.colid )

    Order by Parent_Table,Child_Table

    Thanks,
    Monica

Posting Permissions

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