How to find all the relations between all mysql tables?

Question

How to find all the relations between all MySQL tables? If for example, I want to know the relation of tables in a database of having around 100 tables.

Is there anyway to know this?

Answer 1

The better way, programmatically speaking, is gathering data from INFORMATION_SCHEMA.KEY_COLUMN_USAGE table as follows:

SELECT    `TABLE_SCHEMA`,                          -- Foreign key schema   `TABLE_NAME`,                            -- Foreign key table   `COLUMN_NAME`,                           -- Foreign key column   `REFERENCED_TABLE_SCHEMA`,               -- Origin key schema   `REFERENCED_TABLE_NAME`,                 -- Origin key table   `REFERENCED_COLUMN_NAME`                 -- Origin key column FROM   `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`  -- Will fail if user don't have privilege WHERE   `TABLE_SCHEMA` = SCHEMA()                -- Detect current schema in USE    AND `REFERENCED_TABLE_NAME` IS NOT NULL; -- Only tables with foreign keys 

There are more columns info like ORDINAL_POSITION that could be useful depending your purpose.

More info: http://dev.mysql.com/doc/refman/5.1/en/key-column-usage-table.html

Answer 2

Try this:

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;