SQL SERVER – Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database

UPDATE : SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

This is very long query. Optionally, we can limit the query to return results for one or more than one table.

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='something'WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Next Post
SQL SERVER – Query to Find ByteSize of All the Tables in Database

Related Posts

136 Comments. Leave new

  • GOT IT! (I think) Mark’s solution along with merix’s addition seems to handle all cases well. One of the solutions handled multiple fields but for some reason threw out a lot of the constraints. The code below works but I take NO credit for it.

    SELECT
    CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
    TABLE_CATALOG = FK.TABLE_CATALOG,
    TABLE_SCHEMA = FK.TABLE_SCHEMA,
    TABLE_NAME = FK.TABLE_NAME,
    COLUMN_NAME = FK_COLS.COLUMN_NAME,
    REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
    REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
    REFERENCED_TABLE_NAME = PK.TABLE_NAME,
    REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    AND FK.CONSTRAINT_TYPE = ‘FOREIGN KEY’
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
    AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
    AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    AND PK.CONSTRAINT_TYPE = ‘PRIMARY KEY’
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME AND FK_COLS.ORDINAL_POSITION = PK_COLS.ORDINAL_POSITION

    Reply
  • David Sacker
    June 1, 2013 4:24 am

    And, if you want to query off the sys tables rather than information_schema, this might be up your alley. It is narrow in scope (assumes “dbo” for schema, doesn’t list schema, etc), but it can certainly be enhanced to do so. It runs for one table at a time, but it gives both the parent key relationships, as well as other tables that are foreign keys to ‘YourTableName’.

    SELECT Fk.name AS [Constraint],
    object_name(Fk.parent_object_id) AS FK_table,
    col_name(Fk.parent_object_id, Fk_Cl.parent_column_id)
    AS FK_column,
    TbR.name AS PK_table,
    col_name(Fk.referenced_object_id, Fk_Cl.referenced_column_id)
    AS PK_column
    FROM sys.foreign_keys Fk
    LEFT JOIN sys.tables TbR
    ON TbR.object_id = Fk.referenced_object_id
    JOIN sys.foreign_key_columns Fk_Cl
    ON Fk_Cl.constraint_object_id = Fk.object_id
    WHERE Fk.parent_object_id = object_id(‘YourTableName’)
    OR Fk.referenced_object_id = object_id(‘YourTableName’)
    ORDER BY CASE
    WHEN object_name(Fk.parent_object_id) = ‘YourTableName’ THEN ‘0’
    ELSE object_name(Fk.parent_object_id)
    END,
    CONVERT(int,
    CASE
    WHEN ISNUMERIC(REPLACE(Fk.name, object_name(Fk.parent_object_id) + ‘FK’, ”)) = 0 THEN ‘0’
    ELSE REPLACE(Fk.name, object_name(Fk.parent_object_id) + ‘FK’, ”)
    END),
    CASE
    WHEN object_name(Fk.parent_object_id) = ‘item’ THEN TbR.name
    ELSE object_name(Fk.parent_object_id)
    END

    Reply
  • Ferruccio Guicciardi
    June 3, 2013 4:20 pm

    Thanks Dave, very handy, specially when putting together ETL process to populate Data Warehouses (Kimball models).

    Reply
  • Scott Pearce
    July 12, 2013 6:05 am

    All of these scripts are wrong.

    You are assuming the foreign key relationships link to a primary key field. It is quite possible that a foreign key relationship link to an index with a unique constraint.

    Reply
  • Scott Pearce
    July 12, 2013 7:48 am

    Here is a working script:

    SELECT ForeignKeys.name [ForeignKeyName], PrimaryKeyTable.name [PrimaryTableName], PrimaryKeyColumn.name [PrimaryColumnName],
    ForeignKeyTable.name [ReferenceTableName], ForeignKeyColumn.name [ReferenceColumnName],
    ForeignKeys.update_referential_action_desc [UpdateAction], ForeignKeys.delete_referential_action_desc [DeleteAction]
    FROM sys.foreign_keys ForeignKeys
    JOIN sys.foreign_key_columns ForeignKeyRelationships ON (ForeignKeys.object_id = ForeignKeyRelationships.constraint_object_id)
    JOIN sys.tables ForeignKeyTable ON ForeignKeyRelationships.parent_object_id = ForeignKeyTable.object_id
    JOIN sys.columns ForeignKeyColumn ON (ForeignKeyTable.object_id = ForeignKeyColumn.object_id AND ForeignKeyRelationships.parent_column_id = ForeignKeyColumn.column_id)
    JOIN sys.tables PrimaryKeyTable ON ForeignKeyRelationships.referenced_object_id = PrimaryKeyTable.object_id
    JOIN sys.columns PrimaryKeyColumn ON (PrimaryKeyTable.object_id = PrimaryKeyColumn.object_id AND ForeignKeyRelationships.referenced_column_id = PrimaryKeyColumn.column_id)
    ORDER BY ForeignKeys.name

    Reply
  • Excellent Pinal ..Always I used to search your posts by thinking that I will get a perfect Answer…

    Reply
  • Hi
    I have a MySQL database that consists of 40 tables and is located on.
    My knowledge of the database that is on a piece of code I am not able to find all Relationships database.
    When using the following code fragment shows an error message .
    Piece of code :

    SELECT
    Table_Name = FK.TABLE_NAME,
    Primary_Key = PT.COLUMN_NAME,
    Foreign_Key = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_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

    Error message :

    #1052 – Column ‘Table_Name’ in field list is ambiguous

    Please use the code please ?
    Thanks

    Reply
    • the name table_name is part of more than two tables. You need to qualify it with table alias name like pk.table_name

      Reply
  • Paul Marshall
    July 14, 2014 2:22 pm

    Hi. Thanks for all your help over the years by the way. One small comment on the order by. To make it relational you should not use the ordinal position of the column, you should use the column name or alias.

    Reply
  • great script. i use several scripts from this site which are extremely helpful!
    thanks!

    Reply
  • Hi!

    I was searching a similar query, but yours is not working if the FK is referring to a non primary-key or if there are multiple columns in it.

    I leave my solution here, if

    SELECT DISTINCT
    tc.constraint_name,
    tc.constraint_type,
    tc.table_name,
    kcu.column_name,
    tc.is_deferrable,
    tc.initially_deferred,
    rc.match_option AS match_type,
    rc.update_rule AS on_update,
    rc.delete_rule AS on_delete,
    ccu.table_name AS references_table,
    ccu.column_name AS references_field
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
    ON tc.constraint_catalog = rc.constraint_catalog
    AND tc.constraint_schema = rc.constraint_schema
    AND tc.constraint_name = rc.constraint_name
    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.constraint_catalog = kcu.constraint_catalog
    aND tc.constraint_schema = kcu.constraint_schema
    AND tc.constraint_name = kcu.constraint_name
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ccu
    ON rc.unique_constraint_catalog = ccu.constraint_catalog
    AND rc.unique_constraint_schema = ccu.constraint_schema
    AND rc.unique_constraint_name = ccu.constraint_name
    AND kcu.ordinal_position = ccu.ordinal_position
    WHERE tc.constraint_type = ‘FOREIGN KEY’
    AND tc.table_name = ‘some_table_name’
    AND tc.constraint_name = ‘some_fk_name’
    AND tc.constraint_catalog = ‘some_catalog_name’

    Of course, configure the WHERE with your own conditions.

    And thank you for the initial share, which gave me the start way to find my solution.

    Reply
  • SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
    PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
    PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
    PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
    FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
    FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
    FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
    FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
    — Force the column to be non-nullable (see SQL BU 325751)
    –KEY_SEQ = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
    UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,’CnstIsUpdateCascade’)
    WHEN 1 THEN 0
    ELSE 1
    END),
    DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,’CnstIsDeleteCascade’)
    WHEN 1 THEN 0
    ELSE 1
    END),
    FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
    PK_NAME = CONVERT(SYSNAME,I.NAME),
    DEFERRABILITY = CONVERT(SMALLINT,7) — SQL_NOT_DEFERRABLE
    FROM SYS.ALL_OBJECTS O1,
    SYS.ALL_OBJECTS O2,
    SYS.ALL_COLUMNS C1,
    SYS.ALL_COLUMNS C2,
    SYS.FOREIGN_KEYS F
    INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
    ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
    INNER JOIN SYS.INDEXES I
    ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
    AND F.KEY_INDEX_ID = I.INDEX_ID)
    WHERE O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
    AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
    AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
    AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
    AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
    AND C2.COLUMN_ID = K.PARENT_COLUMN_ID

    Reply
  • Hi Pinal,

    I use this to get most of the needed metadata if I don’t have access to the Diagram

    select ic.TABLE_CATALOG
    ,ic.TABLE_NAME
    ,ic.COLUMN_NAME
    ,ic.IS_NULLABLE
    ,ic.DATA_TYPE
    ,ic.CHARACTER_MAXIMUM_LENGTH
    ,it.CONSTRAINT_NAME
    ,rf.UPDATE_RULE
    ,rf.DELETE_RULE
    from information_schema.columns ic
    left join information_schema.KEY_COLUMN_USAGE it
    left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rf on it.CONSTRAINT_NAME = rf.CONSTRAINT_NAME
    on it.COLUMN_NAME=ic.COLUMN_NAME
    where
    it.TABLE_NAME = ic.TABLE_NAME or it.TABLE_NAME is null
    order by ic.TABLE_NAME, it.CONSTRAINT_NAME desc, ic.COLUMN_NAME;

    Reply
  • Hi,
    This is really great….
    One thing I notice is first column must be FK_Table I guess…

    Reply
  • Question: I have more tables in my database than the query resulted. There is a FK I specifically wanted to find the relations to but the table in which it is a FK didn’t even show up in results. Is there a restriction or constraint in the query?
    Sorry if this sounds too silly. I am a beginner.
    Thanks!

    Reply
  • Very Nice Script

    Reply
  • muhammad Jassam
    October 27, 2016 12:38 am

    CREATE TABLE `student_history`(
    `student_history_id` int(11) not null AUTO_INCREMENT,
    `student_id` int(11) unsigned not null ,
    `class_id` int(11) unsigned not null,
    `section_id` int(11) unsigned not null,
    `roll_number` int(15) not null,
    `admission_year` date() not null,
    PRIMARY KEY(`student_history_id`),
    CONSTRAINT `fk_student` FOREIGN KEY (`student_id`)
    REFERENCES `student`(`student_id`),
    CONSTRAINT `fk_class` FOREIGN KEY (`class_id`)
    REFERENCES `classes`(`class_id`),
    CONSTRAINT `fk_section` FOREIGN KEY (`section_id`)
    REFERENCES `section`(`section_id`)

    )ENGINE=INNODB charset=utf8;

    Reply
  • muhammad Jassam
    October 27, 2016 12:39 am

    my query is not executed.

    Reply
    • One thing is missing from this function. The number of rows that exists for each relations. How could it be added? I’ve been looking for a solution for a while, with no results.

      Reply
  • Ronak Bagadia
    May 25, 2018 10:33 pm

    Very helpful.

    Reply

Leave a Reply

Menu