The best answers to the question “How can I list all foreign keys referencing a given table in SQL Server?” in the category Dev.
I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table?
(SQL answers preferable over clicking about in the GUI of the management studio.)
I’d use the Database Diagramming feature in SQL Server Management Studio, but since you ruled that out – this worked for me in SQL Server 2008 (don’t have 2005).
To get list of referring table and column names…
select t.name as TableWithForeignKey, fk.constraint_column_id as FK_PartNo, c. name as ForeignKeyColumn from sys.foreign_key_columns as fk inner join sys.tables as t on fk.parent_object_id = t.object_id inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id where fk.referenced_object_id = (select object_id from sys.tables where name="TableOthersForeignKeyInto") order by TableWithForeignKey, FK_PartNo
To get names of foreign key constraints
select distinct name from sys.objects where object_id in ( select fk.constraint_object_id from sys.foreign_key_columns as fk where fk.referenced_object_id = (select object_id from sys.tables where name="TableOthersForeignKeyInto") )
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.
Try this :
This gives you:
- The FK itself 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
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