Obtain all foreign key information

By querying the system table, you can obtain all the foreign key information in a database. After the information is obtained, you can generate some scripts, such as deleting all foreign keys in a table, generate a new foreign key based on the existing foreign key information.

Select FK. Name fkname, Constable. Name constablename, conscol. Name conscolname, reftable. Name reftablename, refcol. Name refcolname
From SYS. foreign_keys FK
Join SYS. Objects constable On FK. parent_object_id = Constable. Object_id
Join SYS. Objects reftable On FK. referenced_object_id = Reftable. Object_id
Join SYS. foreign_key_columns fkc On Fkc. constraint_object_id = FK. Object_id  
Join SYS. Columns conscol On Fkc. parent_column_id = Conscol. column_id And Fkc. parent_object_id = Conscol. Object_id
Join SYS. Columns refcol On Fkc. referenced_column_id = Refcol. column_id And Fkc. referenced_object_id = Refcol. Object_id

The above query results show that the foreign key name, foreign key base table, foreign key column, foreign key reference table, and foreign key reference column.

Run the following statement to delete the tblname foreign key in the database:

Use DB
Go

Declare @ References_name Nvarchar(100),
@ Table_name Nvarchar(100)

Declare Cursor_references Cursor   For
Select FK. Name fkname, Constable. Name constable From SYS. foreign_keys FK
Join SYS. Objects constable On FK. parent_object_id = Constable. Object_id
Join SYS. Objects reftable On FK. referenced_object_id = Reftable. Object_id
Join SYS. foreign_key_columns fkc On Fkc. constraint_object_id = FK. Object_id  
Join SYS. Columns conscol On Fkc. parent_column_id = Conscol. column_id And Fkc. parent_object_id = Conscol. Object_id
Join SYS. Columns refcol On Fkc. referenced_column_id = Refcol. column_id And Fkc. referenced_object_id = Refcol. Object_id
Where Reftable. Name = ' Tblname '
Open Cursor_references
Fetch   Next   From Cursor_references Into   @ References_name , @ Table_name
While   @ Fetch_status   =   0
Begin
Exec ( ' ALTER TABLE ' + @ Table_name + ' Drop Constraint ' + @ References_name )
Fetch   Next   From Cursor_references Into   @ References_name , @ Table_name
End
Close Cursor_references
Deallocate Cursor_references