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
Inner Join with Count() on Three Tables
Instead of Null How to Show '0' in Result with Select Statement SQL
Do Ddl Statements Always Give You an Implicit Commit, or Can You Get an Implicit Rollback
How to View All the Metadata of Columns of a Table in Oracle Database
Finding Rows with Consecutive Increase in the Values of a Column
Case Statement in SQL, How to Return Multiple Variables
In MySQL: How to Pass a Table Name as Stored Procedure And/Or Function Argument
SQL Server Management Studio - How to Change a Field Type Without Dropping Table
Deleting Duplicates Rows from Redshift
Postgresql With-Delete "Relation Does Not Exists"
Using Oracle SQL, How Does One Output Day Number of Week and Day of Week
Running a Stored Procedure with Nodejs and Mssql Package Error
What Is the SQL for 'Next' and 'Previous' in a Table
Selecting Specific Row Number in SQL
Libraries for Ado.Net to Rapidly Bulk Insert Data into a Database from a .CSV File
Autoincrement Fields on Databases Without Autoincrement Field