How To Find PRIMARY and FOREIGN KEYS In The Database

Below Query helps us to list out all the Primary & Foreign Keys of the database - This also provides the FK Table, FK Column, PK Table , PK Column along with the PRIMARY KEY Constraints.


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




Hope it helps !!!