SQL Script to Find Foreign Keys to a Specific Table

How can I find out what FOREIGN KEY constraint references a table in SQL Server?

Here it is:

SELECT 
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = 'YourTableName'

This way, you'll get the referencing table and column name.

Edited to use sys.tables instead of generic sys.objects as per comment suggestion.
Thanks, marc_s

How can I list all foreign keys referencing a given table in SQL Server?

Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table:

EXEC sp_fkeys 'TableName'

You can also specify the schema:

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

Without specifying the schema, the docs state the following:

If pktable_owner is not specified, the default table visibility rules
of the underlying DBMS apply.

In SQL Server, if the current user owns a table with the specified
name, that table's columns are returned. If pktable_owner is not
specified and the current user does not own a table with the specified
pktable_name, the procedure looks for a table with the specified
pktable_name owned by the database owner. If one exists, that table's
columns are returned.

SQL Script to find Foreign keys to a specific table?

Courtesy of Pinal Dave:

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

PostgreSQL: SQL script to get a list of all foreign key references to a table

You have to JOIN pg_constraint to pg_attribute and un nest the column arrays (could be compound keys) to get the referenced column name in the base table.
You have to use pg_class to get the table names.
pg_get_constraintdef gives you the actual SQL line that is used to create the constraint.

SELECT (select  r.relname from pg_class r where r.oid = c.confrelid) as base_table,
       a.attname as base_col,
       (select r.relname from pg_class r where r.oid = c.conrelid) as referencing_table,
       UNNEST((select array_agg(attname) from pg_attribute where attrelid = c.conrelid and array[attnum] <@ c.conkey)) as referencing_col,
       pg_get_constraintdef(c.oid) contraint_sql
  FROM pg_constraint c join pg_attribute a on c.confrelid=a.attrelid and a.attnum = ANY(confkey)
 WHERE c.confrelid = (select oid from pg_class where relname = 'breeds')
   AND c.confrelid!=c.conrelid;

How do I see all foreign keys to a table or column?

For a Table:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>';

For a Column:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>' AND
  REFERENCED_COLUMN_NAME = '<column>';

Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.

How to list table foreign keys

You can do this via the information_schema tables. For example:

SELECT
    tc.table_schema, 
    tc.constraint_name, 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
      AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';

Find all foreign keys constraints in database referencing a certain primary key

Look at How to find foreign key dependencies in SQL Server?

You can sort on PK_Table and PK_Column to get what you want

---

Related Topics