Select a list of keys of a table in MySQL

Question

Say I have a table "products" and I would like to check if this table has any indexes, foreign keys etc

A "DESCRIBE products" would give some information.

Field        Type           Null    Key    Default   Extra
productCode  varchar(200)   NO      MUL    NULL  
description  varchar(500)   NO             NULL 

Mainly the key field in the case. But defenietly no references and what table is linked to who etc etc.

What is the best way to get such information via SQL about a table?

Thanks

Answer 1

You can use

SHOW CREATE TABLE products

which gives you the query for creating the table. The most informative.

Answer 2

SHOW INDEXES IN <tablename> will give all the indexes in that table.

Answer 3

I don't know about foreign keys, but SHOW INDEX FROM tablename; gives a lot of easily parsed information.

Answer 4

The best way is to use the INFORMATION_SCHEMA as documented here:

http://dev.mysql.com/doc/refman/5.5/en/information-schema.html

Answer 5

$result = mysql_query("SHOW FIELDS FROM $DATABASE.$TABLE_NAME");

$i = 0;

while ($row = mysql_fetch_array($result)) {
  echo $row['Field'] . ' ' . $row['Type'];
}