number of rows in foreign key

script to list the number of foreign key instances existed in Foreign key table referenced to their corresponding Primary Keys. This will helps to test the data during migration.

SET NOCOUNT ON SELECT 'CREATE TABLE #REF(PK_TBL VARCHAR(900),PK_COLS VARCHAR(900),FK_TBL VARCHAR(900),FK_COLS VARCHAR(900),FK_VAL VARCHAR(900) ,CNT INT)'; with CTE_REFERENCES(FK_COLS,FK_TBL,PK_COLS,PK_TBL) AS ( select distinct replace(replace(replace( (SELECT Y.name from sys.sysforeignkeys X left outer join syscolumns Y on X.fkeyid = Y.id and X.fkey = Y.colid WHERE X.constid= A.constid FOR xml PATH ('')) ,'<name>','') ,'</name>',',') + '$' ,',$','')as FK_COLS, SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) as FK_TBL, replace(replace(replace( (SELECT N.name from sys.sysforeignkeys L left outer join syscolumns N on L.Rkeyid = N.id and L.Rkey = N.colid WHERE L.constid= A.constid FOR xml PATH ('')) ,'<name>','') ,'</name>',',') + '$' ,',$','') as PK_COLS , + SCHEMA_NAME(SOF_R.SCHEMA_ID) +'.'+ object_name(rkeyid) as PK_TBL from sys.sysforeignkeys A left outer join syscolumns b on a.fkeyid = b.id and a.fkey = b.colid left outer join syscolumns c on a.rkeyid = c.id and a.rkey = c.colid left outer JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID left outer JOIN sys.objects SOF_R ON SOF_R.OBJECT_ID = A.RKEYID ) SELECT 'INSERT INTO #REF SELECT ' + ''''+ PK_TBL + ''',' + ''''+ PK_COLS + ''',' + ''''+ FK_TBL + ''',' + ''''+FK_COLS +''',' + CASE WHEN CHARINDEX(',',FK_COLS ) = 0 THEN FK_COLS ELSE REPLACE( 'CAST(' + left(FK_COLS,CHARINDEX(',',FK_COLS )-1) +' AS VARCHAR(MAX)),CAST(' + SUBSTRING(FK_COLS,CHARINDEX(',',FK_COLS )+1,LEN(FK_COLS)) +' AS VARCHAR(MAX))' ,',','+'',''+' ) END + ',COUNT(*)'+ ' FROM ' + FK_TBL + ' WHERE CHECKSUM(' + FK_COLS + ') IN ( SELECT CHECKSUM(' + PK_COLS +') FROM '+ PK_TBL + ') GROUP BY ' +FK_COLS FROM CTE_REFERENCES PRINT 'SELECT PK_TBL AS [-SUMMARY_REPORT- PK_TBL ],PK_COLS ,FK_TBL ,FK_COLS ,COUNT(CNT)FROM #REF GROUP BY PK_TBL ,PK_COLS ,FK_TBL ,FK_COLS ;' PRINT 'SELECT PK_TBL AS [-DETAILED_REPORT- PK_TBL ],PK_COLS ,FK_TBL ,FK_COLS ,CNT FROM #REF;' SET NOCOUNT OFF