How to find foreign key dependencies in SQL Server

Question

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

User · Answer

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     lt tableName gt    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   lt tableName gt      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

User · Answer

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 lt  gt 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

User · Answer

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

User · Answer

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

User · Answer

try  sp help  table name   you will get all information about table  including all foreign keys

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[.net] How to find foreign key dependencies in SQL Server?

Examples related to .net

Examples related to sql-server

Examples related to foreign-keys