Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Check if Constraint is enabled or disabled

By roberto armellin,

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

Total article views: 1124 | Views in the last 30 days: 7
 
Related Articles
FORUM

Creating a Check Constraint

Check Constraint

FORUM

Modify Check constraint

Modify Check constraint

BLOG

Untrusted Check Constraints

Check constraints are useful for more than just verifying the data that goes into your tables. The q...

FORUM

Check Constraint

Behaviour of Check Constraint within View and Table

FORUM

Check Constraint with Case Statement

Check Constraint with Case Statement

Tags
 
Contribute