How to disable Constraints for all the tables and

问题:

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.

回答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?

回答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"

回答3:

declare @tname varchar(128), @tschema varchar(128);

declare tables cursor for
select TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.TABLES;

open tables;

fetch next from tables
    into @tschema, @tname;

while @@FETCH_STATUS = 0
begin
    execute ('alter table [' + @tschema + '].[' + @tname + '] nocheck constraint all');
    fetch next from tables
        into @tschema, @tname;
end;

close tables;

deallocate tables;

回答4:

Try to use this command

ALTER TABLE table_Name NOCHECK CONSTRAINT all

to disable all constraint for your tables, and do it for all your 10 tables , but before that check if you haven't put any Delete_Cascade on your tables because the error which is shown maybe because of sub_tables dependencies too. if it didn't work try to disable specific constraint by this command, it maybe two or three extra dependencies.

ALTER TABLE tableName NOCHECK CONSTRAINT constraintName