Mysql – the best approach to find all fields that has (Foreign Key and Primary Key) constraints in a Mysql database

constraintforeign keyMySQLprimary-key

I have a mysql database that has 250 tables.
I would like to run a query to retrieve:
tableName ; FieldName ; ConstaintName (Primary or foreign key).

I could use information_schema.TABLE_CONSTRAINTS to retrieve the constarint type
but for the inner join I have no idea what should I use.

What is the best approach to achieve this task?

EDIT:
not Constraint name. Should be CONSTRAINT_TYPE instead of ConstaintName. So we should be able to see (Primary or foreign key) only.

Best Answer

You can use INFORMATION_SCHEMA KEY_COLUMN_USAGE table.

SELECT
table_name AS TableName,
column_name AS ColumnName,
Constraint_name as ConstraintName
FROM
information_schema.key_column_usage
WHERE table_schema = 'database_name'

Updated code based on OP's requirement:

SELECT DISTINCT(TC.table_name) AS TableName, KC.column_name as ColumnName,
TC. CONSTRAINT_TYPE AS ConstraintType
FROM 
information_schema.table_constraints TC,  
information_schema.key_column_usage KC  
WHERE
TC.CONSTRAINT_TYPE IN ('FOREIGN KEY', 'PRIMARY KEY') 
and KC.table_name = TC.table_name and KC.TABLE_SCHEMA = TC.TABLE_SCHEMA
and KC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
and KC.table_schema = DATABASE();
Related Question