1

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.

1

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();
  • Hi Friend, you are correct but I commited a mistake. I dont want the ConstaintName. I need the value of the CONSTRAINT_TYPE column. – zwitterion Nov 8 '15 at 0:26
  • Hey Friend, that works fine. – zwitterion Nov 9 '15 at 14:21
  • @zwitterion Sounds good.. – sql_handle Nov 9 '15 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.