CODEWITHSUNDEEP
sqlpostgresql
St.Antario
St.Antario

Reputation: 27385

How to find all tables having a specific foreign key?

PostgreSQL 9.4

I have the table called mailing in the schema mailing:

id      name
PK    varcahr(32)

Is it possible to find all tables in all schemas having a foreign key to mailing.id?

Upvotes: 2

Views: 130

Answers (1)

Biswabid
Biswabid

Reputation: 1411

there is a query i use to get the list of foreignkeys:
following query should help:


    SELECT RC.CONSTRAINT_NAME FK_Name
    , KF.TABLE_SCHEMA FK_Schema
    , KF.TABLE_NAME FK_Table
    , KF.COLUMN_NAME FK_Column
    , RC.UNIQUE_CONSTRAINT_NAME PK_Name
    , KP.TABLE_SCHEMA PK_Schema
    , KP.TABLE_NAME PK_Table
    , KP.COLUMN_NAME PK_Column
    , RC.MATCH_OPTION MatchOption
    , RC.UPDATE_RULE UpdateRule
    , RC.DELETE_RULE DeleteRule
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
    WHERE KP.COLUMN_NAME='ID'

Upvotes: 1

Related Questions