How to find all constraints.

SQL Command for Enabling Constraints

In SQL Server, sometimes you need to enable constraints that are not trusted. You can accomplish this using the following SQL command, which identifies the constraints that need to be re-enabled and generates the necessary commands to fix them.

The SQL query below helps you find foreign key and check constraints that are not trusted, and it provides a script to re-enable them.

SELECT 
 SCHEMA_NAME(s.[schema_id]) AS [Schema], 
 OBJECT_NAME(fk.parent_object_id) AS [Table], 
 fk.[name] AS [Constraint],
 CASE is_not_trusted WHEN 1 THEN 'No' ELSE 'Yes' END AS Trusted, 
 fk.[Type_desc], 
 ('ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + 
 QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + 
 ' WITH CHECK CHECK CONSTRAINT ' + fk.name) AS SQLcmdConstraint
FROM sys.foreign_keys fk
   INNER JOIN sys.objects o ON fk.parent_object_id = o.OBJECT_ID
   INNER JOIN sys.schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE fk.is_not_trusted = 1 AND fk.is_not_for_replication = 0
UNION ALL
SELECT 
 SCHEMA_NAME(s.[schema_id]) AS [Schema], 
 OBJECT_NAME(cc.parent_object_id) AS [Table], 
 cc.[name] AS [Constraint],
 CASE is_not_trusted WHEN 1 THEN 'No' ELSE 'Yes' END AS Trusted, 
 cc.[type_desc], 
 ('ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + 
 QUOTENAME(OBJECT_NAME(cc.parent_object_id)) + 
 ' WITH CHECK CHECK CONSTRAINT ' + cc.name) AS col1
FROM sys.check_constraints cc
   INNER JOIN sys.objects o ON cc.parent_object_id = o.OBJECT_ID
   INNER JOIN sys.schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE cc.is_not_trusted = 1 AND cc.is_not_for_replication = 0 AND cc.is_disabled = 0;

Automating the Process with a Cursor

If you want to automatically enable all the constraints, you can use a cursor. This script will loop through each constraint and execute the necessary SQL to trust it.

Here’s the SQL script for automating the process:

DECLARE @entrust_constraint NVARCHAR(1000);
DECLARE Cursor1 CURSOR FOR
 SELECT 
  ('ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + 
  QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + 
  ' WITH CHECK CHECK CONSTRAINT ' + fk.[Name]) AS [EntrustTheConstraint]
 FROM [sys].foreign_keys fk
 INNER JOIN [sys].objects o ON fk.parent_object_id = o.OBJECT_ID
 INNER JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
 WHERE fk.is_not_trusted = 1 AND fk.is_not_for_replication = 0
UNION ALL
 SELECT 
  ('ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + 
  QUOTENAME(OBJECT_NAME(cc.parent_object_id)) + 
  ' WITH CHECK CHECK CONSTRAINT ' + cc.[Name]) AS [EntrustTheConstraint]
 FROM [sys].check_constraints cc
 INNER JOIN [sys].objects o ON cc.parent_object_id = o.OBJECT_ID
 INNER JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
 WHERE cc.is_not_trusted = 1 AND cc.is_not_for_replication = 0 AND cc.is_disabled = 0;

OPEN Cursor1;
FETCH NEXT FROM Cursor1 INTO @entrust_constraint;
WHILE (@FETCH_STATUS = 0)
BEGIN
  BEGIN TRY
   EXECUTE sp_executesql @entrust_constraint;
   PRINT 'Successed: ' + @entrust_constraint;
  END TRY
  BEGIN CATCH
   PRINT 'Failed: ' + @entrust_constraint;
  END CATCH;
  FETCH NEXT FROM Cursor1 INTO @entrust_constraint;
END;
CLOSE Cursor1;
DEALLOCATE Cursor1;

This cursor script will automatically run through the constraints that are not trusted and attempt to enable them. If it’s successful, you’ll see a success message for each constraint, or if it fails, an error message will be printed.

Conclusion

If you're managing databases with many foreign key and check constraints, using these SQL scripts will save you a lot of time and effort. You can run them periodically to ensure that all your constraints are properly trusted and functional.