LISTING 3: Cursor to Find Rows That Might Violate a Foreign Key Relationship

USE tempdb
GO

DECLARE violation_cursor CURSOR
READ_ONLY FOR
  SELECT c1.column_name   AS COLUMN_NAME,
         c1.table_name    AS PK_TABLE_NAME,
         c2.table_name    AS FK_TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c1 INNER JOIN 
INFORMATION_SCHEMA.COLUMNS c2
            ON c1.COLUMN_NAME = c2.COLUMN_NAME
               AND c1.DATA_TYPE = c2.DATA_TYPE
               AND c1.TABLE_NAME <> c2.TABLE_NAME
         INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
            ON c1.COLUMN_NAME = ccu.COLUMN_NAME
               AND c1.TABLE_NAME = ccu.TABLE_NAME
         INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
            ON ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
         INNER JOIN INFORMATION_SCHEMA.TABLES T
            ON t.TABLE_NAME = c2.TABLE_NAME
   WHERE tc.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')   -- Anything a foreign
                                                           -- key can reference
         AND t.TABLE_TYPE = 'BASE TABLE'                   -- Eliminate views.
         AND t.TABLE_NAME NOT LIKE 'sys%'                 -- Eliminate system tables.

DECLARE @foreignkeytable    VARCHAR (128)
DECLARE @primarykeytable   VARCHAR (128)
DECLARE @primarykeyfield    VARCHAR (128)

OPEN violation_cursor

FETCH NEXT FROM violation_cursor INTO @primarykeyfield, @primarykeytable, 
@foreignkeytable
WHILE (@@fetch_status <> -1)
BEGIN
   IF (@@fetch_status <> -2)
   BEGIN
      DECLARE @dynamicsqlstring NVARCHAR (4000)

      SET @dynamicsqlstring = ' SELECT *' +
                                ' FROM [' + @foreignkeytable + ']' +
                                ' WHERE [' + @primarykeyfield + '] NOT IN (' +
                                ' SELECT [' + @primarykeyfield + ']' +
                                ' FROM [' + @primarykeytable + '] )' + CHAR(10)
 
      PRINT @dynamicsqlstring
--    PRINT REPLACE (@dynamicsqlstring, ' NOT IN ', ' IN ')
 
      EXEC sp_executesql @dynamicsqlstring
   END
   FETCH NEXT FROM violation_cursor INTO @primarykeyfield, @primarykeytable, 
@foreignkeytable
END

CLOSE violation_cursor
DEALLOCATE violation_cursor

GO