View all foreign key constraints for entire MySQL database

I have a large database with over 150 tables that I've recently been handed. I'm just wondering if there is an easy way to view all foreign key constraints for the entire DB instead of on a per-table basis.

You can use the [`INFORMATION_SCHEMA`][1] tables for this. For example, the [`INFORMATION_SCHEMA TABLE_CONSTRAINTS`][2] table.

Something like this should do it:

select *
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'FOREIGN KEY'


[1]:

[To see links please register here]

[2]:

[To see links please register here]

**SQL:**

select constraint_name,
table_schema,
table_name
from information_schema.table_constraints
where constraint_schema = 'astdb'

**Output:**

+----------------------------+--------------+---------------------+
| constraint_name | table_schema | table_name |
+----------------------------+--------------+---------------------+
| PRIMARY | astdb | asset_category |
| PRIMARY | astdb | asset_type |
| PRIMARY | astdb | asset_valuation |
| PRIMARY | astdb | assets |
| PRIMARY | astdb | com_mst |
| PRIMARY | astdb | com_typ |
| PRIMARY | astdb | ref_company_type |
| PRIMARY | astdb | supplier |
| PRIMARY | astdb | third_party_company |
| third_party_company_ibfk_1 | astdb | third_party_company |
| PRIMARY | astdb | user |
| PRIMARY | astdb | user_role |
+----------------------------+--------------+---------------------+

Query this code


select constraint_name,
table_schema,
table_name
from information_schema.table_constraints

You will get constraint_name, and filter the table_schema which is the list of `database `.

[Look at This][1]


[1]:

[To see links please register here]

This is what I prefer to get useful informations:

SELECT CONSTRAINT_NAME,
UNIQUE_CONSTRAINT_NAME,
MATCH_OPTION,
UPDATE_RULE,
DELETE_RULE,
TABLE_NAME,
REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'your_database_name'

The currently accepted answer by user RedFilter will work fine if you have just 1 database, but not if you have many.

After entering `use information_schema;` use this query to get foreign keys for `name_of_db`:

select * from `table_constraints` where `table_schema` like `name_of_db` and `constraint_type` = 'FOREIGN KEY'

Use this query to get foreign keys for `name_of_db` saved to world-writeable file `output_filepath_and_name`:


select * from `table_constraints` where `table_schema` like "name_of_db" and `constraint_type` = 'FOREIGN KEY' into outfile "output_filepath_and_name" FIELDS TERMINATED BY ',' ENCLOSED BY '"';