All geek questions in one place

How to remove all foreign keys from a SQL Server database?

I want to delete all foreign keys that have the following conditions.

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME IN ('Table1', 'Table2') AND CONSTRAINT_NAME LIKE '%FK__%__DL%'
+5
source share
1 answer

There is a table called INFORMATION_SCHEMA.TABLE_CONSTRAINTS that stores all table restrictions. the constraint type FOREIGN KEY also stored in this table. Thus, by filtering this type, you can span all foreign keys.

 SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

If you create a dynamic query (for a DROP internal key) to modify a table, you can achieve the goal of changing constraints for all tables.

 WHILE(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME IN ('Table1', 'Table2') AND CONSTRAINT_NAME LIKE '%FK__%__DL%')) BEGIN DECLARE @sql_alterTable_fk NVARCHAR(2000) SELECT TOP 1 @sql_alterTable_fk = ('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']') FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME IN ('Table1', 'Table2') AND CONSTRAINT_NAME LIKE '%FK__%__DL%' EXEC (@sql_alterTable_fk) END

EXISTS function with its parameter guarantees that there is at least one delimiter for the foreign key.

+6
source

Source: https://habr.com/ru/post/1265953/

More articles:


All Articles