Wednesday, August 11, 2004 9:58 PM
by
RepeatableRead
Is that Constraint turned on or off?
Ever have the nagging feeling that one of your Default or Foreign-Key constraints in a nasty/inherited database has been turned off? How would you know if it was turned on or off?
I have. And since SQL Server has to be able to keep track of that info, it only followed that hidden somewhere within the system tables there'd be a way to find out. Here's how I solved it using a bit of undocumented info in the status field of the sysconstraints table:
SELECT
OBJECT_name(id) [Table],
OBJECT_NAME(constid) [Constraint]
FROM
sysconstraints
WHERE
status & 0x4000 = 0x4000
Much better. With this piece of code that nagging feeling is either going to be squelched or confirmed...