Find constraint disabled in SQL Server

Question

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

Answer 1

Use the system views for this:

select * from sys.check_constraints
where is_disabled = 1;

Answer 2

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;

Answer 3

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

Answer 4

Here is the query which lists all constraints from a connected database.

select table_view,
    object_type, 
    constraint_type,
    constraint_name,
    is_enforced,
    details
from (
    select schema_name(t.schema_id) + '.' + t.[name] as table_view, 
        case when t.[type] = 'U' then 'Table'
            when t.[type] = 'V' then 'View'
            end as [object_type],
        case when c.[type] = 'PK' then 'Primary key'
            when c.[type] = 'UQ' then 'Unique constraint'
            when i.[type] = 1 then 'Unique clustered index'
            when i.type = 2 then 'Unique index'
            end as constraint_type, 
        isnull(c.[name], i.[name]) as constraint_name,
        c.is_enforced as is_enforced,
        substring(column_names, 1, len(column_names)-1) as [details]
    from sys.objects t
        left outer join sys.indexes i
            on t.object_id = i.object_id
        left outer join sys.key_constraints c
            on i.object_id = c.parent_object_id 
            and i.index_id = c.unique_index_id
       cross apply (select col.[name] + ', '
                        from sys.index_columns ic
                            inner join sys.columns col
                                on ic.object_id = col.object_id
                                and ic.column_id = col.column_id
                        where ic.object_id = t.object_id
                            and ic.index_id = i.index_id
                                order by col.column_id
                                for xml path ('') ) D (column_names)
    where is_unique = 1
    and t.is_ms_shipped <> 1
    union all 
    select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
        'Table',
        'Foreign key',
        fk.name as fk_constraint_name,
        case when fk.is_disabled = 0 then 1 else 0 end as is_enforced,
        schema_name(pk_tab.schema_id) + '.' + pk_tab.name
    from sys.foreign_keys fk
        inner join sys.tables fk_tab
            on fk_tab.object_id = fk.parent_object_id
        inner join sys.tables pk_tab
            on pk_tab.object_id = fk.referenced_object_id
        inner join sys.foreign_key_columns fk_cols
            on fk_cols.constraint_object_id = fk.object_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Check constraint',
        con.[name] as constraint_name,
        case when con.is_disabled = 0 then 1 else 0 end as is_enforced,
        con.[definition]
    from sys.check_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Default constraint',
        con.[name],
        '1' as is_enforced,
        col.[name] + ' = ' + con.[definition]
    from sys.default_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id) a
where is_enforced = 0
order by table_view, constraint_type, constraint_name