View all foreign key constraints for entire MySQL database
You can use the INFORMATION_SCHEMA
tables for this. For example, the INFORMATION_SCHEMA TABLE_CONSTRAINTS
table.
Something like this should do it:
select *from INFORMATION_SCHEMA.TABLE_CONSTRAINTSwhere CONSTRAINT_TYPE = 'FOREIGN KEY'
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_NAMEFROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSWHERE 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 '"';