title: How to know if a Constraint is actually with CHECK in SQL Server
body: <p>I have to check a Constraint in case it was created with NOCHECK; the thing is that some of the constrained data has been fixed and checked after, so I need to check each constraint only if the check is still disabled. For example I have this code;</p>
  
  <p>CODE1 (OK):</p>
  
  <pre><code>ALTER TABLE [dbo].[TABLE1]  WITH NOCHECK ADD  CONSTRAINT [FK_MYFK] FOREIGN KEY([TABLE1_id], [TABLE1_Codigo])
  REFERENCES [dbo].[TABLE2] ([TABLE2_id], [TABLE2_Codigo])
  GO
  ALTER TABLE [dbo].[TABLE1] CHECK CONSTRAINT [FK_MYFK]
  GO
  </code></pre>
  
  <p>Here the Check was added after;</p>
  
  <p>CODE 2 (With the NOCHECK Bug i need to fix):</p>
  
  <pre><code>ALTER TABLE [dbo].[TABLE1]  WITH NOCHECK ADD  CONSTRAINT [FK_MYFK] FOREIGN KEY([TABLE1_id], [TABLE1_Codigo])
  REFERENCES [dbo].[TABLE2] ([TABLE2_id], [TABLE2_Codigo])
  GO
  ALTER TABLE [dbo].[TABLE1] NOCHECK CONSTRAINT [FK_MYFK]
  GO
  </code></pre>
  
  <p>So I need to know if the constraint has the CHECK</p>
  
  <p>I Have Tried this but as it was created with the NOCHECK it always returns that my contraint is not trusted.</p>
  
  <pre><code>SELECT name, tbl = object_name(parent_obj)
  FROM sysobjects
  WHERE objectproperty(id, 'CnstIsNotTrusted') = 1
  </code></pre>
  
  <p>Is there a way (that does not include to drop and create the constraint again) to know if a constraint is actually with CHECK despite that it was created with NOCHECK?</p>