• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6619
  • Last Modified:

Query to list table's foreign key constraints

Is there a query that I can run to list all tables that depend on a certain table

e.g.  all tables that depend on tblData

They currently have a foreign key defined and I would like a way to quickly list these tables.
0
mock5c
Asked:
mock5c
  • 2
  • 2
1 Solution
 
ZippitCommented:
credit to here:
http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/
SELECT
       K_Table  = FK.TABLE_NAME,
       FK_Column = CU.COLUMN_NAME,
       PK_Table  = PK.TABLE_NAME,
       PK_Column = PT.COLUMN_NAME,
       Constraint_Name = C.CONSTRAINT_NAME
FROM       INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN      INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN      INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN  (
       SELECT      i1.TABLE_NAME, i2.COLUMN_NAME
       FROM        INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
           INNER JOIN      INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
               WHERE       i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
       ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
--ORDER BY
--       1,2,3,4
WHERE      PK.TABLE_NAME='tbl_MyTable' 

Open in new window

0
 
ZippitCommented:
oh..sorry...that was for MSSQL...I just noticed your question is in regards to Postgresql...my appologies
0
 
mock5cAuthor Commented:
They query can be run under postgres.  However, many of my constraint names are simply $1, $2, etc.  I take it that it would be impossible to figure out using that query?
0
 
mock5cAuthor Commented:
The query only required a minor modification to work with Postgres.  The beginning section

K_Table  = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table  = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME

had to be changed to:

FK.TABLE_NAME as K_Table,
CU.COLUMN_NAME as FK_Column,
PK.TABLE_NAME as PK_Table,
PT.COLUMN_NAME as PK_Column,
C.CONSTRAINT_NAME as Constraint_Name
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now