Disabled Foreign Keys
I recently ran into an issue where a former developer that worked with me had disabled a bunch of foreign keys and forgotten to re-enable them. I should say that I don't generally approve of disabling foreign keys at all, although there may be some context I haven't thought of that would justify it. I did some digging to find a complete list of foreign keys that were disabled, and came up with this:
DECLARE @Result TABLE
(
ndx int IDENTITY NOT NULL PRIMARY KEY,
TableName varchar(50),
ForeignKeyName varchar(100),
Disabled bit
)
INSERT INTO @Result (TableName, ForeignKeyName, Disabled)
SELECT
obj.name,
FK.name,
ObjectProperty(object_id(FK.name), 'CnstIsDisabled') Disabled
FROM sysobjects FK
INNER JOIN sysforeignkeys FKS ON FK.id = FKS.constid
INNER JOIN sysobjects obj ON obj.id = FKS.fKeyID
WHERE ObjectProperty(object_id(FK.name), 'CnstIsDisabled') =1
SELECT *
FROM @RESULT
GO
I then added a field to my temp table and built some SQL to correct any unkeyed data, and to drop and recreate the keys.