0

I'm doing an audit of constraints in SQL Server.

How to find all constraints that are not enabled?

This, after using the next declaration:

alter table mytable nocheck constraint all
3

Use the system views for this:

select * from sys.check_constraints
where is_disabled = 1;
3
SELECT 
    [object] = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name), 
    [disabled_constraint] = c.name
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.schema_id = s.schema_id
  INNER JOIN sys.check_constraints AS c
  ON t.object_id = c.parent_object_id
  WHERE c.is_disabled = 1;
1

Mike and Aaron are both on the right track - but the question asks for ALL constraints that are disabled - not just CHECK constraints (i.e., FKs can be disabled too).

Moreover, FKs can also be UNTRUSTED - which will yield the potential for not just bad/invalid DATA but can/will also lead to perf issues (as SQL Server can't/won't trust these FKs when creating execution plans) as per: https://sqlserverfast.com/blog/hugo/2007/03/can-you-trust-your-constraints/

As such, I tend to use the following:

    WITH constraints AS ( 
    SELECT
        QUOTENAME(SCHEMA_NAME(t.[schema_id])) + N'.' + QUOTENAME(t.[name]) [table] ,
        c.[type_desc] [constraint_type],
        c.[name] [constraint_name]
    FROM
        sys.tables AS t
        INNER JOIN sys.check_constraints AS c ON t.[object_id] = c.parent_object_id
    WHERE
        c.is_disabled = 1

    UNION 

    SELECT 
        QUOTENAME(SCHEMA_NAME([schema_id])) + N'.' + QUOTENAME(OBJECT_NAME([parent_object_id])) [table],
        CASE WHEN is_disabled = 1 THEN 'FOREIGN_KEY (DISABLED)' ELSE 'FOREIGN_KEY (UNTRUSTED)' END [constraint_type],
        [name] [constraint_name]
    FROM 
        sys.[foreign_keys] 
    WHERE 
        [is_disabled] = 1 OR [is_not_trusted] = 1
)

SELECT 
    [table],
    [constraint_type],
    [constraint_name] 
FROM 
    constraints
ORDER BY 
    [constraint_type], [table];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.