Posted By: chillitom
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.)
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.