I want to point at three more things.
Firstly, Kapil_KK's answer contains a typo. It should be 'NOCHECK' instead of 'NO CHECK'.
Secondly, you cannot disable default constraints, you can only disable foreign key constraints and check constraints.
Thirdly, after disabling a constraint and enabling it again, it is considered 'not trusted' by the optimizer, which can have a negative performance impact. You should make the constraints trusted again.
-- Disable all FK and CHECK constraints
ALTER TABLE <TableName> NOCHECK CONSTRAINT ALL;
GO
-- Proof that they are disabled
SELECT * FROM sys.foreign_keys WHERE is_disabled = 1;
SELECT * FROM sys.check_constraints WHERE is_disabled = 1;
GO
-- Enable all FK and CHECK constraints again
ALTER TABLE <TableName> CHECK CONSTRAINT ALL;
GO
-- Proof that they are enabled
SELECT * FROM sys.foreign_keys WHERE is_disabled = 1;
SELECT * FROM sys.check_constraints WHERE is_disabled = 1;
GO
-- Proof that they are not trusted
SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1;
SELECT * FROM sys.check_constraints WHERE is_not_trusted = 1;
GO
-- Make them trusted again
-- The possibility exists that data prevents a constraint to become trusted
ALTER TABLE <TableName> WITH CHECK CHECK CONSTRAINT ALL;
GO
-- Proof that your constraints are trusted again
SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1;
SELECT * FROM sys.check_constraints WHERE is_not_trusted = 1;
GO
Just to give you an extra option, I include some code to enable FK constraints one by one with a cursor and dynamic SQL
DECLARE cr_EnableFKs CURSOR FOR
SELECT 'ALTER TABLE ' + OBJECT_NAME(parent_object_id) + ' CHECK CONSTRAINT ' + Name
FROM sys.foreign_keys
ORDER BY OBJECT_NAME(parent_object_id), Name
FOR READ ONLY;
DECLARE @Statement VarChar(255);
OPEN cr_EnableFKs;
FETCH cr_EnableFKs INTO @Statement;
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE(@Statement);
FETCH cr_EnableFKs INTO @Statement;
END;
CLOSE cr_EnableFKs;
DEALLOCATE cr_EnableFKs;
IF EXISTS ( SELECT 1 FROM sys.foreign_keys WHERE is_disabled = 1 )
BEGIN
SELECT name AS FK_Name, 'ERROR! FK not enabled' AS Remark FROM sys.foreign_keys WHERE is_disabled = 1;
RAISERROR(N'Not all foreign keys were enabled!', 11, 1);
END;
GO
-- End of script
