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.

Shreeves

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?

Separate

I have tried to disable and DROP table but that table is Not getting dropped and saying referenced by FK constraint.., – Shreeves

@Pink you have tried DROP statement before re-enabling the CONSTRAINTs ? – Sneer

I have Disable constraint and DROP table, when disabled it must drop without error but its again showing "Could not drop object 'DropTable' because it is referenced by a FOREIGN KEY constraint." – Shreeves

@Out: Ah, you also have keys referencing the table on other tables. It doesn't suffice to remove them on the table you want to drop. That you can't drop the table or otherwise orphan the data is kind of the idea of foreign keys. – Separate

Why doesn't the work? You are essentially disabling all constraints on all table right? – Banker

@Nautatava Navlakha: Yes, the question asks for it, doesn't it. – Separate

Under "Edit" above it says "If disabling the constraints is not enough, you will have to drop the constraints". I wonder when that is the case, when is it not enough to do EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL" ? – Ethnography

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"

Cheryllches

Answer 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;

Nemathelminth

Just change "nocheck constraint all" to "check constraint all" to enable constraints for all tables. – Nemathelminth

When i executed these query it is successful, but still when i tried to drop table it could not drop because of FK constraint. My aim is to drop few tables without FK errors, Just disable FK and drop table – Shreeves

I add the "where schemaTables.TABLE_TYPE = 'BASE TABLE'" to the query to get only table and it works for me. – Navigation

Answer 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

Otiliaotina

Answer 5

I am thinking of dropping all constraints instead of disabling, but I wanted a copy of their names and constraint types first:

--List out all Constraints
SELECT '[' + OBJECT_SCHEMA_NAME(parent_obj) + '].[' + object_name(parent_obj) +']'  parent_obj  --DISTINCT tbl = object_name(parent_obj), 'EXEC sp_msforeachtable "ALTER TABLE ' + object_name(parent_obj) + ' NOCHECK CONSTRAINT all"'
 , '[' + OBJECT_SCHEMA_NAME(id) + '].[' + object_name(id) +']' as [obj_name], xtype, objectproperty(id, 'CnstIsDisabled') CnstIsDisabled,
' ALTER TABLE ' + '[' + OBJECT_SCHEMA_NAME(id) + '].[' + object_name(id) +']' + ' NOCHECK CONSTRAINT ALL' As DisableConstraintScript
FROM sysobjects
WHERE objectproperty(id, 'CnstIsDisabled') = 0
ORDER BY OBJECT_SCHEMA_NAME(parent_obj) + '.' + object_name(parent_obj)

--xtype:
--D = Default Value
--F = Foreign Key
--UQ = Unique Key
--PK = Primary Key
--C = Constraint

parent_obj          obj_name                    xtype CnstIsDisabled    DisableConstraintScript
[dbo].[AdminAudit]  [dbo].[PK_AuditIDIndentity] PK    0               ALTER TABLE [dbo].[PK_AuditIDIndentity] NOCHECK CONSTRAINT ALL

Bifocal