Verifying Foreign Keys in MS SQL Server

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:

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.