CODEWITHSUNDEEP
.netsql-serverforeign-keys
Even Mien
Even Mien

Reputation: 45858

How to find foreign key dependencies in SQL Server?

How can I find all of the foreign key dependencies on a particular column?

What are the different alternatives (graphically in SSMS, queries/views in SQL Server, 3rd party database tools, code in .NET)?

Upvotes: 173

Views: 205958

Answers (13)

Divya Agrawal
Divya Agrawal

Reputation: 285

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

It will give you:

The FK itself Schema that the FK belongs to

  • The "referencing table" or the table that has the FK
  • The "referencing column" or the column inside referencing table that points to the FK
  • The "referenced table" or the table that has the key column that your FK is pointing to
  • The "referenced column" or the column that is the key that your FK is pointing to

Upvotes: 0

Shreyansh Bothra
Shreyansh Bothra

Reputation: 56

You can Use INFORMATION_SCHEMA.KEY_COLUMN_USAGE and sys.foreign_key_columns in order to get the foreign key metadata for a table i.e. Constraint name, Reference table and Reference column etc.

Below is the query:

SELECT  CONSTRAINT_NAME, COLUMN_NAME, ParentTableName, RefTableName,RefColName FROM 
    (SELECT CONSTRAINT_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '<tableName>') constraint_details
    INNER JOIN  
    (SELECT ParentTableName, RefTableName,name ,COL_NAME(fc.referenced_object_id,fc.referenced_column_id) RefColName  FROM (SELECT object_name(parent_object_id) ParentTableName,object_name(referenced_object_id) RefTableName,name,OBJECT_ID  FROM sys.foreign_keys WHERE parent_object_id = object_id('<tableName>') ) f 
    INNER JOIN   
    sys.foreign_key_columns AS fc  ON  f.OBJECT_ID = fc.constraint_object_id ) foreign_key_detail 
    on foreign_key_detail.name = constraint_details.CONSTRAINT_NAME

Upvotes: 2

Michael
Michael

Reputation: 2046

Because your question is geared towards a single table, you can use this:

EXEC sp_fkeys 'TableName'

I found it on SO here:

https://stackoverflow.com/a/12956348/652519

I found the information I needed pretty quickly. It lists the foreign key's table, column and name.

EDIT

Here's a link to the documentation that details the different parameters that can be used: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql

Upvotes: 44

profimedica
profimedica

Reputation: 2830

After long search I found a working solution. My database does not use the sys.foreign_key_columns and the information_schema.key_column_usage only contain primary keys.

I use SQL Server 2015

SOLUTION 1 (rarely used)

If other solutions does not work, this will work fine:

        WITH CTE AS
        (
            SELECT 
                TAB.schema_id,
                TAB.name,
                COL.name AS COLNAME,
                COl.is_identity
            FROM 
                sys.tables TAB INNER JOIN sys.columns COL 
                    ON TAB.object_id = COL.object_id
        )
        SELECT 
            DB_NAME() AS [Database], 
            SCHEMA_NAME(Child.schema_id) AS 'Schema',
            Child.name AS 'ChildTable',
            Child.COLNAME AS 'ChildColumn',
            Parent.name AS 'ParentTable',
            Parent.COLNAME AS 'ParentColumn'
        FROM 
            cte Child INNER JOIN CTE Parent
                ON 
                    Child.COLNAME=Parent.COLNAME AND 
                    Child.name<>Parent.name AND 
                    Child.is_identity+1=Parent.is_identity

SOLUTION 2 (commonly used)

In most of the cases this will work just fine:

        SELECT
            DB_NAME() AS [Database], 
            SCHEMA_NAME(fk.schema_id) AS 'Schema',
            fk.name 'Name',
            tp.name 'ParentTable',
            cp.name 'ParentColumn',
            cp.column_id,
            tr.name 'ChildTable',
            cr.name 'ChildColumn',
            cr.column_id
        FROM
            sys.foreign_keys fk
        INNER JOIN
            sys.tables tp ON fk.parent_object_id = tp.object_id
        INNER JOIN
            sys.tables tr ON fk.referenced_object_id = tr.object_id
        INNER JOIN
            sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        INNER JOIN
            sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
        INNER JOIN
            sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
        WHERE 
            -- CONCAT(SCHEMA_NAME(fk.schema_id), '.', tp.name, '.', cp.name) LIKE '%my_table_name%' OR
            -- CONCAT(SCHEMA_NAME(fk.schema_id), '.', tr.name, '.', cr.name) LIKE '%my_table_name%' 
        ORDER BY
            tp.name, cp.column_id

Upvotes: 3

John Sansom
John Sansom

Reputation: 41819

The following query will help to get you started. It lists all Foreign Key Relationships within the current database.

SELECT
    FK_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

You can also view relationships graphically within SQL Server Management studio within Database Diagrams.

Upvotes: 308

Homo Programmatoris
Homo Programmatoris

Reputation: 76

Just a note for @"John Sansom" answer,

If the foreign key dependencies are sought, I think that the PT Where clause should be:

i1.CONSTRAINT_TYPE = 'FOREIGN KEY'  -- instead of 'PRIMARY KEY'

and its the ON condition:

ON PT.TABLE_NAME = FK.TABLE_NAME – instead of PK.TABLE_NAME

As commonly is used the primary key of the foreign table, I think this issue has not been noticed before.

Upvotes: 1

Kumait
Kumait

Reputation: 702

This query will return details about foreign keys in a table, it supports multiple column keys.

    SELECT *
    FROM
    (
    SELECT 
    T1.constraint_name ConstraintName,
    T2.COLUMN_NAME ColumnName,
    T3.TABLE_NAME RefTableName, 
    T3.COLUMN_NAME RefColumnName,
    T1.MATCH_OPTION MatchOption, 
    T1.UPDATE_RULE UpdateRule, 
    T1.DELETE_RULE DeleteRule
    FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS T1
    INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE T2 
    ON T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME
    INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE T3 
    ON T1.UNIQUE_CONSTRAINT_NAME = T3.CONSTRAINT_NAME 
    AND T2.ORDINAL_POSITION = T3.ORDINAL_POSITION) A
    WHERE A.ConstraintName = 'table_name'

Upvotes: 3

Sierramike
Sierramike

Reputation: 481

Thanks so much to John Sansom, his query is terrific !

In addition : you should add " AND PT.ORDINAL_POSITION = CU.ORDINAL_POSITION" at the end of your query.

If you have multiple fields in primary key, this statement will match the corresponding fields to each other (I had the case, your query did create all combinations, so for 2 fields in primary key, I had 4 results for the corresponding foreign key).

(Sorry I can't comment John's answer as I don't have enough reputation points).

Upvotes: 5

Mimmo
Mimmo

Reputation:

I think this script is less expensive:

SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Upvotes: 33

Timothy Walden
Timothy Walden

Reputation: 676

If you plan on deleting or renaming a table or column finding only foreign key dependencies might not be enough.

Referencing tables not connected with foreign key - You’ll also need to search for referencing tables that might not be connected with foreign key (I’ve seen many databases with bad design that didn’t have foreign keys defined but that did have related data). Solution might be to search for column name in all tables and look for similar columns.

Other database objects – this is probably a bit off topic but if you were looking for all references than it’s also important to check for dependent objects.

GUI Tools – Try SSMS “Find related objects” option or tools such as ApexSQL Search (free tool, integrates into SSMS) to identify all dependent objects including tables connected with foreign key.

Upvotes: 39

dave
dave

Reputation: 1

USE information_schema;

SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM KEY_COLUMN_USAGE
WHERE (table_name = *tablename*) AND NOT (REFERENCED_TABLE_NAME IS NULL)

Upvotes: -1

Andrija
Andrija

Reputation: 14473

try: sp_help [table_name]

you will get all information about table, including all foreign keys

Upvotes: 104

TheTXI
TheTXI

Reputation: 37875

One that I really like to use is called SQL Dependency Tracker by Red Gate Software. You can put in any database object(s) such as tables, stored procedures, etc. and it will then automatically draw the relationship lines between all the other objects that rely on your selected item(s).

Gives a very good graphical representation of the dependencies in your schema.

Upvotes: 6

Related Questions