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

Spudhead · #1 (**permalink**) 02-17-09, 13:19

... 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.

r937 · #2 (**permalink**) 02-17-09, 13:57

Code:

SHOW CREATE TABLE shoutout

this will show the foreign key syntax, e.g.

Code:

CONSTRAINT w00t_fk
    FOREIGN KEY ( w00t )
      REFERENCES w00ts ( id )

Spudhead · #3 (**permalink**) 02-18-09, 06:06

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?

shammat · #4 (**permalink**) 02-18-09, 06:34

Check out the INFORMATION_SCHEMA

Spudhead · #5 (**permalink**) 02-18-09, 06:59

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

r937 · #6 (**permalink**) 02-18-09, 08:38

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:

shammat · #7 (**permalink**) 02-18-09, 11:25

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:

Quote:

SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

from within your regular connection?