Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2008
    Posts
    6

    Unanswered: Retrieving Foreign Key Meta Data (SQL2K5)

    Hi.

    I know I can access the sys.foreign_keys table to retrieve foreign keys for all tables within a database and subsequently use the parent_object_id to identify the table for which the foreign key has reference.

    But can anyone tell me how I can retrieve the column in that table for which the foreign key is referencing?

    So far all I can imagine to do is use the foreign key name in effort of deciphering the column for which it references, but this isn't a good method since not everyone follows standard naming conventions for creating foreign keys or if a default key name is given because the user doesn't provide one.

    Can anybody provide code that will build off of the sys.foreign_keys table to retrieve the referenced column name for the key?

    Any help at all would be much appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    wouldn't you need to just query for the primary key in the sys tables or the information schema views?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2008
    Posts
    6
    Thanks, but I am sure I totally follow your recommendation?

    How would I retrieve the "column name" that a foreign key is referencing by using any of the tables or views you've provided???

  4. #4
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    is this what you're looking for?

    Code:
    SELECT OBJECT_NAME(PARENT_OBJECT_ID)        TABLE_NAME,
           PT.NAME                              FIELD_NAME,
           OBJECT_NAME(REFERENCED_OBJECT_ID)    REFTABLE_NAME,
           FT.NAME                              REFFIELD_NAME
    FROM   SYS.FOREIGN_KEY_COLUMNS FKC
           JOIN SYS.COLUMNS PT
             ON FKC.PARENT_OBJECT_ID = PT.OBJECT_ID
                AND FKC.PARENT_COLUMN_ID = PT.COLUMN_ID
           JOIN SYS.COLUMNS FT
             ON FKC.REFERENCED_OBJECT_ID = FT.OBJECT_ID
                AND FKC.REFERENCED_COLUMN_ID = FT.COLUMN_ID

  5. #5
    Join Date
    Jun 2008
    Posts
    6
    Yes! Thanks rohitkumar that is EXACTLY what I needed.
    Really appreciated.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    correction. that is what you wanted. what you needed is a different matter. it certainly would have taken you a lot less time to write that out myself than the 1:20 it took you to get your response and then you may have learned something.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    If he/she is willing to learn then it does not matter if we give just a hint or the full answer.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I read those words but they convey no message. Let me try again. Nope.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    never mind

  10. #10
    Join Date
    Jun 2008
    Posts
    6
    Thrasymachus, are you telling me that you were purposely throwing bread crumbs???

    I have learned more from rohitkumar's example for how I can manipulate those objects to get what I actually needed which by the way wasn't the query the raw query as he provided, but a variation of it.

    I was in desparate need of a solution which I still had to derive for myself but his example was EXTREMELY helpful in providing the solution I needed.

    While I agree with you (only on a very slim bias) on sometimes providing clues, it was absolutely selfish of you to discredit someone elses response because you want to throw hints instead.

    Rohitkumar, thank you again tremendously for you response. I was on a very tight deadline to derive a solution to dynamically recreate constraints in our environment after a full drop of the constraints on all tables in a database. While your response wasn't the entire solution, it was a very critical piece of information you provided for us to move forward quickly.

    Thrasymachus, stick to your own methods and be happy but don't rudely criticize others for providing assistance in another way. Under the circumstances the information provided was VERY much appreciated by many.

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    2 types of people in this world. those who work and those who get by on the work of others. the split is about 5 to 95. which side are you on?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sean - you are right back to your grumpy old self in your new gig.
    TBF I suspect the OP would have been perfectly happy with SYS.FOREIGN_KEY_COLUMNS as an answer - (s)he just got more than required - not his\her fault.

  13. #13
    Join Date
    Jun 2008
    Posts
    6
    That is entirely correct, SYS.FOREIGN_KEY_COLUMNS would have also been a great help.

    On a closing note. I don't imagine you (Thr..) to be a scholar on philosophy nor a master of statistics with you 5/95 split theory.

    But here's another for you:
    It's said that there are 2 potential sides of a problem, those who are a part of the solution and those that are part of the problem.

    Since your comments haven't been very helpful and didn't contribute to the solution, what category would you imagine you fall in this time???

    Good day.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Initiating Thread Lock (with extreme prejudice)
    Authorisation code: 0hP00t13W3L0veY0u

    Moderator comment:
    Problem solved
    Further posts unlikely to contribute further to the world wide web of informational
    Unilateral moderator action undertaken

    Lock status: Completified

    Thread status: Lockened

    Moderator status: In the pipe, 555

Posting Permissions

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