Check if Constraint is enabled or disabled

,

This is a script to find all constraints and check if they are enabled or disabled.

select 
     [Table]     = o2.name, 
     [Constraint] = o.name, 
     [Enabled]   = case when ((C.Status & 0x4000)) = 0 then 1 else 0 end
from sys.sysconstraints C
     inner join sys.sysobjects o on  o.id = c.constid -- and o.xtype='F'
     inner join sys.sysobjects o2 on o2.id = o.parent_obj
You can check sysconstraints documentation to find other status.
You can test it by enabling or disabilig constraint with
ALTER TABLE <<tablename>> NOCHECK CONSTRAINT all
ALTER TABLE <<tablename>> CHECK CONSTRAINT all
select 
 [Table] = o2.name, 
 [Constraint] = o.name, 
 [Enabled] = case when ((C.Status & 0x4000)) = 0 then 1 else 0 end
from sys.sysconstraints C
 inner join sys.sysobjects o on o.id = c.constid -- and o.xtype='F'
 inner join sys.sysobjects o2 on o2.id = o.parent_obj

Rate

4.25 (4)

Share

Share

Rate

4.25 (4)