?
Solved

Checking if Constraint is on

Posted on 2011-11-01
6
Medium Priority
?
325 Views
Last Modified: 2012-05-12


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'
0
Comment
Question by:25112
6 Comments
 
LVL 12

Expert Comment

by:NormanMaina
ID: 37063024
Try
SELECT * FROM sys.objects WHERE type='D'

Open in new window

0
 
LVL 15

Accepted Solution

by:
Anuj earned 1336 total points
ID: 37063224

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
 
LVL 5

Author Comment

by:25112
ID: 37063876
anujnb, that was it. can you spin that on sql 2005 sys.objects?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 664 total points
ID: 37065530
SELECT name, CASE WHEN OBJECTPROPERTY(object_id, 'CnstIsDisabled') THEN ...
FROM sys.objects
WHERE type IN ('c', 'd', 'f', 'uq')
ORDER BY name
0
 
LVL 15

Assisted Solution

by:Anuj
Anuj earned 1336 total points
ID: 37068497

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
 
LVL 5

Author Comment

by:25112
ID: 37069714
thank you.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question