Today I was checking one Demo database and surprised to see that foreign key columns contained some values which were not stored in the referenced tables. The foreign key constraints were there, but they had been deactivated with the WITH NOCHECK clause.
The below script verify the foreign key constraint:
The below script verify the foreign key constraint:
DECLARE @TableName sysname, @ForeignKey sysname, @String NVARCHAR(1000)
DECLARE cur CURSOR FOR
SELECT t.name, fk.name
FROM sys.foreign_keys fk
INNER JOIN sys.objects t ON t.object_id = fk.parent_object_id
ORDER BY t.name, fk.name
OPEN cur
FETCH cur INTO @TableName, @ForeignKey
WHILE @@FETCH_STATUS = 0 BEGIN
SET @String = 'ALTER TABLE ' + @TableName +
' WITH CHECK CHECK CONSTRAINT ' + @ForeignKey
PRINT @String
BEGIN TRY
EXEC (@String)
END TRY
BEGIN CATCH
PRINT 'Conflict in ' + @TableName + N'.' + @ForeignKey
DBCC CHECKCONSTRAINTS (@TableName)
END CATCH
FETCH cur INTO @TableName, @ForeignKey
END
CLOSE cur
DEALLOCATE cur
SELECT t.name, fk.name, fk.is_disabled, fk.is_not_trusted
FROM sys.foreign_keys fk
INNER JOIN sys.objects t ON t.object_id = fk.parent_object_id
WHERE fk.is_not_trusted = 1
ORDER BY t.name, fk.name
The script tries to enable each foreign key constraint it finds in the database using the WITH CHECK CHECK CONSTRAINT clause.
If the check fails, it calls DBCC CHECKCONSTRAINTS which returns the set
of recordings violating the foreign key constraint.
The result of the script execution shows you which tables and records need to be cleaned up.
Run the script repeatedly after data cleanup until a single empty result set is returned.
