Menu
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.)
760
The most Simplest one is by using sys. foreign_keys_columns in SQL. Here the table contains the Object ids of all the foreign keys wrt their Referenced column ID Referenced Table ID as well as the Referencing Columns and Tables.
To see foreign key relationships of 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 = 'db_name' AND REFERENCED_TABLE_NAME = 'table_name';
Using SQL Server Management StudioIn Object Explorer, expand Databases, expand a database, and then expand Tables. Right-click a table, and then click View Dependencies.
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.
147
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
