Checking if Constraint is on



how can we check if the constraint has a check or nocheck right now? I could not see it from
SELECT * FROM sys.objects WHERE type_desc LIKE '%CONSTRAINT'
LVL 5
25112Asked:
Who is Participating?
 
AnujSQL Server DBACommented:

SELECT name, OBJECT_NAME(parent_obj), CASE WHEN OBJECTPROPERTY(ID,'CnstIsDisabled') = 1 THEN 'Disabled' ELSE 'Enabled' END DisableStatus
FROM sysobjects
WHERE xtype IN ('c','d','f','uq')
0
 
Norman MainaCommented:
Try
SELECT * FROM sys.objects WHERE type='D'

Open in new window

0
 
25112Author Commented:
anujnb, that was it. can you spin that on sql 2005 sys.objects?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
SELECT name, CASE WHEN OBJECTPROPERTY(object_id, 'CnstIsDisabled') THEN ...
FROM sys.objects
WHERE type IN ('c', 'd', 'f', 'uq')
ORDER BY name
0
 
AnujSQL Server DBACommented:

SELECT name, OBJECT_NAME(parent_object_id) TableName, CASE WHEN OBJECTPROPERTY(OBJECT_ID,'CnstIsDisabled') = 1 THEN 'Disabled' ELSE 'Enabled' END DisableStatus
FROM sys.objects
WHERE type IN ('c','d','f','uq')

sysobjects will be removed in future versions of sql server, so as you guessed sys.objects is the right choice.


 
0
 
25112Author Commented:
thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.