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;

Answer 3

Try

SELECT
`TABLE_NAME`,
`COLUMN_NAME`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE `CONSTRAINT_SCHEMA` = 'YOUR_DATABASE_NAME' AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL AND
`REFERENCED_TABLE_NAME` IS NOT NULL AND
`REFERENCED_COLUMN_NAME` IS NOT NULL

do not forget to replace YOUR_DATABASE_NAME with your database name!

Answer 4

A quick method of visualizing relationships in MySQL is reverse engineering the database with MySQL Workbench.

This can be done using the reverse engineering too, which will result in an entity-relationship diagram much like the following (though you may have to organize it yourself, once it is generated):

Answer 5

SELECT 
    count(1) totalrelationships ,
    c.table_name tablename,
    CONCAT(' ',GROUP_CONCAT(c.column_name ORDER BY ordinal_position SEPARATOR ', ')) columnname,
    CONCAT(' ',GROUP_CONCAT(c.column_type ORDER BY ordinal_position SEPARATOR ', ')) columntype    
FROM
    information_schema.columns c RIGHT JOIN
    (SELECT column_name , column_type FROM information_schema.columns WHERE 
    -- column_key in ('PRI','MUL') AND  -- uncomment this line if you want to see relations only with indexes
    table_schema = DATABASE() AND table_name = 'YourTableName') AS p
    USING (column_name,column_type)
WHERE
    c.table_schema = DATABASE()
    -- AND c.table_name != 'YourTableName'
    GROUP BY tablename
    -- HAVING (locate(' YourColumnName',columnname) > 0) -- uncomment this line to search for specific column 
    ORDER BY totalrelationships desc, columnname
;

Answer 6

Based on xudre's answer, you can execute the following to see all the relations of a schema:

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`
WHERE `TABLE_SCHEMA` = 'YourSchema'
AND   `REFERENCED_TABLE_NAME` IS NOT NULL  -- Only tables with foreign keys

What I want in most cases is to know all FKs that point to a specific table. In this case I run:

SELECT 
  `TABLE_SCHEMA`,                          -- Foreign key schema
  `TABLE_NAME`,                            -- Foreign key table
  `COLUMN_NAME`                            -- Foreign key column
FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`  
WHERE `TABLE_SCHEMA` = 'YourSchema'
AND   `REFERENCED_TABLE_NAME` = 'YourTableName'

Answer 7

1) Go into your database:
use DATABASE;

2) Show all the tables:
show tables;

3) Look at each column of the table to gather what it does and what it's made of:
describe TABLENAME;

4) Describe is nice since you can figure out exactly what your table columns do, but if you would like an even closer look at the data itself: select * from TABLENAME
If you have big tables, then each row usually has an id, in which case I like to do this to just get a few lines of data and not have the terminal overwhelmed:
select * from TABLENAME where id<5 - You can put any condition here you like.

This method give you more information than just doing select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;, and it also provides you with more bite-sized information each time.

EDIT

As the comments suggested, the above WHERE id < 5 was a bad choice as a conditional placeholder. It is not a good idea to limit by ID number, especially since the id is usually not trustworthy to be sequential. Add LIMIT 5 at the end of the query instead.

Answer 8

One option is : You can do reverse engineering to understand it in diagrammatic way.

When you install MySQL, you will get MySQLWorkbench. You need to open it and choose the database you want to reverse engineer. Click on Reverse Engineer option somewhere you find under the tools or Database menu. It will ask you to choose the tables. Either you select the tables you want to understand or choose the entire DB. It will generate a diagram with relationships.

Answer 9

you can use:

SHOW CREATE TABLE table_name;