Here’s a script that reports on your disabled Foreign Key and Check Constraints. It also gives you the command to re-enable them and also a rollback in case you need to disable them again and also the DBCC CHECKCONSTRAINTS command for each in case of any violations you need to identify.
--Foreign keys
SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname,
'ALTER TABLE ' + o.name + ' WITH CHECK CHECK CONSTRAINT ' + i.name AS Command,
'ALTER TABLE ' + o.name + ' NOCHECK CONSTRAINT ' + i.name AS Rollback_command,
'DBCC CHECKCONSTRAINTS(' + i.name + ')' AS 'DBCC'
from sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0;
GO
--Check Constraints
SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname,
'ALTER TABLE ' + o.name + ' WITH CHECK CHECK CONSTRAINT ' + i.name AS Command,
'ALTER TABLE ' + o.name + ' NOCHECK CONSTRAINT ' + i.name AS Rollback_command,
'DBCC CHECKCONSTRAINTS(' + i.name + ')' AS 'DBCC'
from sys.check_constraints i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0;
GO