How to find foreign key dependencies in SQL Server?

Question

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.

try: sp_help [table_name]

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

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://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql

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.

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

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.