How to List Table Foreign Keys

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 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.

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';

How can I list all foreign keys in a Table?

The below code snippet could be put to some use and modified by your further requirement -
This would pull the column level details such as data type, length, precision, scale etc...

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],
    TY.[name] AS system_data_type, col1.[max_length],col1.[precision], col1.[scale], col1.[is_nullable], col1.[is_ansi_padded]
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
INNER JOIN sys.[types] TY ON col1.[system_type_id] = TY.[system_type_id] AND col1.[user_type_id] = TY.[user_type_id] 
AND tab1.name = 'TableName1'

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

MySQL - How to list all tables that have a foreign key referring to my table's primary key?

I guess the last answer was almost correct. just change the table from "key_column" to "key_column_usage.

Cheers

Nikao

How can I get the foreign keys of a table in mysql

Simple way to get foreign keys for given table:

SELECT
    `column_name`, 
    `referenced_table_schema` AS foreign_db, 
    `referenced_table_name` AS foreign_table, 
    `referenced_column_name`  AS foreign_column 
FROM
    `information_schema`.`KEY_COLUMN_USAGE`
WHERE
    `constraint_schema` = SCHEMA()
AND
    `table_name` = 'your-table-name-here'
AND
    `referenced_column_name` IS NOT NULL
ORDER BY
    `column_name`;

How can I select all tables with a certain amount of foreign keys in them?

Run this query on for your target database. Please make sure the query returning correct data by some manual checking before rely on it :)

SELECT [Table Name],COUNT(*)
FROM 
(
    SELECT fk_tab.name as [Table Name],
    fk.name [FK_Name]
    FROM sys.foreign_keys fk
    INNER JOIN sys.tables fk_tab
    ON fk_tab.object_id = fk.parent_object_id
)A
GROUP BY [Table Name]
ORDER BY COUNT(*) DESC

For MySQL, Can your please try this. I simply copied it from Here

select concat(fks.constraint_schema, '.', fks.table_name) as foreign_table,
       '->' as rel,
       concat(fks.unique_constraint_schema, '.', fks.referenced_table_name)
              as primary_table,
       fks.constraint_name,
       group_concat(kcu.column_name
            order by position_in_unique_constraint separator ', ') 
             as fk_columns
from information_schema.referential_constraints fks
join information_schema.key_column_usage kcu
     on fks.constraint_schema = kcu.table_schema
     and fks.table_name = kcu.table_name
     and fks.constraint_name = kcu.constraint_name
group by fks.constraint_schema,
         fks.table_name,
         fks.unique_constraint_schema,
         fks.referenced_table_name,
         fks.constraint_name
order by fks.constraint_schema,
         fks.table_name;