CODEWITHSUNDEEP
mysqldatabaserelationshiprelation
user3148861
user3148861

Reputation: 501

How to find all the relations between all mysql tables?

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?

Upvotes: 50

Views: 118729

Answers (9)

Lucas Basquerotto
Lucas Basquerotto

Reputation: 8053

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'

Upvotes: 5

Roman
Roman

Reputation: 9441

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.

Upvotes: 4

Fanta Sylla
Fanta Sylla

Reputation: 139

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

ERD

Upvotes: 13

Ricardo Sismeiro
Ricardo Sismeiro

Reputation: 61


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
;

Upvotes: 5

balderys
balderys

Reputation: 67

you can use:

SHOW CREATE TABLE table_name;

Upvotes: 1

Mostafa Lavaei
Mostafa Lavaei

Reputation: 2039

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!

Upvotes: 13

xudre
xudre

Reputation: 2771

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

Upvotes: 64

user3655708
user3655708

Reputation: 1

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.

Upvotes: 0

BaBL86
BaBL86

Reputation: 2622

Try this:

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

Upvotes: 26

Related Questions