Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: "SHOW KEYS FROM" - can I get the source table name?

    ... umm.... as it says, really.

    It gives me a list of FK's but not the names of the tables on the other end of those FK's.

    Presumably I'm being a plank and there's a very good reason why it doesn't tell you that, because I'd have thought that the name of the table on the other end of the relationship was one of the first things you'd want to know about a given relationship.


  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SHOW CREATE TABLE shoutout
    this will show the foreign key syntax, e.g.
    Code:
    CONSTRAINT w00t_fk
        FOREIGN KEY ( w00t )
          REFERENCES w00ts ( id )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Thanks - but is there a way that would give me the data in a bunch of fields, rather than one long SQL string that I'm gong to have to somehow parse in my application code?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Check out the INFORMATION_SCHEMA

  5. #5
    Join Date
    Jan 2002
    Posts
    189
    Awesome. The KEY_COLUMN_USAGE table contains what I need, but it's in a different schema to everything else. So I'd need a different ADODB connection to actually do anything with it.

    I don't think I'm going to be able to do this

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Spudhead
    ...to somehow parse in my application code?
    you never said you wanted to automate this

    that information is pretty pertinant to a satisfactory answer, wouldn't you say

    Q: i need some water
    A: here's a cup of water for you
    Q: wrong! i need water to float my yacht!!!
    A:
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Spudhead
    Awesome. The KEY_COLUMN_USAGE table contains what I need, but it's in a different schema to everything else. So I'd need a different ADODB connection to actually do anything with it.
    I don't understand this (but I have no experience with ADODB though)

    Can't you simply run:
    SELECT *
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    from within your regular connection?

Posting Permissions

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