patternsqlMinor

Find constraint disabled in SQL Server

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

findsqldisabledconstraintserver

Problem

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

Solution

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;

Code Snippets

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;

Context

StackExchange Database Administrators Q#103528, answer score: 5

Revisions (0)

No revisions yet.