Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin
Last active November 16, 2021 09:43
Show Gist options
  • Save EitanBlumin/f81d272a70668cc20bb3741396f14b54 to your computer and use it in GitHub Desktop.
Save EitanBlumin/f81d272a70668cc20bb3741396f14b54 to your computer and use it in GitHub Desktop.
Find which records are causing your Foreign Key check to fail (more info: https://eitanblumin.com/2018/11/06/find-and-fix-untrusted-foreign-keys-in-all-databases/ )
/************** Find Orphaned Records **************
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
More info: https://eitanblumin.com/2018/11/06/find-and-fix-untrusted-foreign-keys-in-all-databases/
****************************************************/
DECLARE
@ForeignKeyName SYSNAME = 'FK_MyTable_MyOtherTable'
, @PrintOnly BIT = 0
DECLARE
@FKId INT,
@ChildTableID INT,
@ParentTableID INT,
@CMD NVARCHAR(MAX),
@ColumnNullabilityCheck NVARCHAR(MAX) = N''
SELECT
@FKId = object_id,
@ChildTableID = parent_object_id,
@ParentTableID = referenced_object_id
FROM sys.foreign_keys
WHERE name = @ForeignKeyName
IF @FKId IS NULL
BEGIN
RAISERROR(N'Foreign Key %s was not found in current database!', 16, 1, @ForeignKeyName);
GOTO Quit;
END
SELECT
@CMD = ISNULL(@CMD + CHAR(13) + CHAR(10) + N' AND ', N'') + N'ctable.' + QUOTENAME(cc.name) + N' = ptable.' + QUOTENAME(pc.name)
, @ColumnNullabilityCheck = @ColumnNullabilityCheck + CHAR(13) + CHAR(10) + N' AND ctable.' + QUOTENAME(cc.name) + N' IS NOT NULL'
--ChildTable = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id))
--, ChildColumn = QUOTENAME(cc.name)
--, ParentTable = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id))
--, ParentColumn = QUOTENAME(pc.name)
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS cc
ON fkc.parent_object_id = cc.object_id
AND fkc.parent_column_id = cc.column_id
INNER JOIN sys.columns AS pc
ON fkc.referenced_object_id = pc.object_id
AND fkc.referenced_column_id = pc.column_id
WHERE fkc.constraint_object_id = @FKId
SET @CMD = N'SELECT ctable.*
FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(@ChildTableID)) + '.' + QUOTENAME(OBJECT_NAME(@ChildTableID)) + N' AS ctable
WHERE NOT EXISTS
(SELECT NULL FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(@ParentTableID)) + '.' + QUOTENAME(OBJECT_NAME(@ParentTableID)) + N' AS ptable
WHERE ' + @CMD + N')'
+ ISNULL(@ColumnNullabilityCheck, N'')
PRINT @CMD
IF @PrintOnly = 0
EXEC (@CMD);
Quit:
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment