About this question
I have made a script that, one at a time, deletes all the foreign keys from a database, just like this:
ALTER TABLE MyTable1 DROP CONSTRAINT FK_MyTable1_col1 ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col1 ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col2
What surprises me is that the script takes a long time: on average, 20 seconds for each DROP FK. Now, I understand that creating a FK may be a big deal, because the server has to go and check that the FK constraint is not infringed from the beginning, but dropping? What does a server do when dropping FKs that takes so long? This is both for my own curiosity, and to understand if there is a way to make things faster. Being able to remove FK (not just disable them) would allow me to be much faster during a migration, and therefore minimize downtime. How do I drop a foreign key in SQL Server?