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?

Kessler

use mysql workbench to reverse engineer DB and give you an ER diagram with all relationships – Exaggerated

Except for foreign key constraints, there's nothing in the database that encodes relations. If the programmers haven't provided documentation, it's just in their heads and you need to figure it out empirically. That's why they pay you the big bucks. – Bricole

Possible a duplicate of this. You can extract foreign keys this way. – Sparkie

Hi Satya, can you provide more details? reverse engineer DB is what? – Kessler

Pro tips, just ask your senior who know it. Often it is a mess left by previous people. You only need to know few of those. And probably nobody in company know all Tables also. Ask the documentation or diagram for it, they probably don't have it also. – Siddur

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

Rashida

Answer 2

Try this:

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

Awlwort

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!

Thruster

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):

Stiffler

Not if you are dealing with a legacy code with more than 500 tables! – Adularia

This is cool! Can the relationship also be automatically generated? I only the standalone tables, wonder if that is possible. – Treadle

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
;

Usable

This probably is the better answer out there. You generally want to know relationship of a single table or a single column of a table. Read between the lines before you copy-paste it to your sql terminal. – Rook

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'

Dextro

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.

Parlance

using WHERE id<5 is troublesome when you don't know the ID, and no one can be sure that those IDs are still valid. You should use LIMIT 5 to limit the number of records. – Internode

@Internode You are right. It was a poor choice of WHERE clause – Parlance

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS; works – Alkali

Answer 8

you can use:

SHOW CREATE TABLE table_name;

Unleavened

This does not answer the question – Dispense

Answer 9

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.

Mayest