How to disable Constraints for all the tables and enable it?

Question

I have 60 tables. I want to drop 10 tables where these 10 tables are Constraints(PK,FK) to other 20 tables. While dropping these 10 tables, I need to truncate or delete data from the other 20 tables. Finally I want to disable all 60 table Constraints(FK,PK) and then enable all 60 table constraints after I am done with my work of adding/dropping tables. Is this possible?

When I drop a table it is asking for FK. When I truncate those FK dependencies it also is still showing the same. I don't want to mess with all those FK,PK.

I want to know smarter method.

Answer 1

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL" GO 

You may also want to do this:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL" GO 

To enable them afterwards

EXEC sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL" GO  -- SQL enable all constraints - enable all constraints sql server -- sp_MSforeachtable is an undocumented system stored procedure EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL" GO 

Edit:
If disabling the constraints is not enough, you will have to drop the constraints.

If you're dropping and recreating the tables, you will have to recreate the foreign key constrains afterwards.

If you just need to drop the constrains, you might find this useful:
SQL DROP TABLE foreign key constraint

If you need to write a script to drop and create the constraints, you might find my post here more useful:
SQL Server: Howto get foreign key reference from information_schema?

Answer 2

To disable you can apply this:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" 

To enable:

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"