SQL-Postgres Database Community Blog: 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

SQL-Postgres Database Community Blog

main header

Monday, October 19, 2009

number of rows in foreign key

1 comment:

Featured Post

SQL Server AWS Migration BCP

Contributors

Report Abuse