CODEWITHSUNDEEP
mysqlforeign-keys
yodann
yodann

Reputation: 355

mysql: list all relation view related to column in a table

How to list all relation (Foreign key) of a column in a table? Say I have table user, with PK user_id, and I want to know all tables related to this table using the PK column.

Upvotes: 0

Views: 43

Answers (1)

user8406805
user8406805

Reputation:

Below is the SQL statement to get the details for all the foreign key for given PK (table_name, column_name). Hopefully, it works for you. 

SELECT  TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_SCHEMA,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE REFERENCED_TABLE_NAME = 'user'
    AND REFERENCED_COLUMN_NAME ='user_id'

Upvotes: 1

Related Questions