Friday, May 16, 2014

SQL Server - Coding - Find out tables those are related

Environment: Tried on SQL Server 2008

First Query: Show all tables from the database where parent-child relationship is present 

SELECT
OBJECT_NAME(rkeyid) parent_table,
OBJECT_NAME(fkeyid) child_table,
OBJECT_NAME(constid) fkey_name,
c1.name fkey_col,
c2.name ref_keycol
FROM SYS.SYSFOREIGNKEYS s
INNER JOIN SYS.SYSCOLUMNS c1
ON ( s.fkeyid = c1.id and s.fkey = c1.colid )
INNER JOIN SYSCOLUMNS c2
ON ( s.rkeyid = c2.id and s.rkey = c2.colid )

ORDER BY parent_table,child_table
;


Second Query: Show all parent tables of a particular table

SELECT
OBJECT_NAME(rkeyid) parent_table,
OBJECT_NAME(fkeyid) child_table,
OBJECT_NAME(constid) fkey_name,
c1.name fkey_col,
c2.name ref_keycol
FROM SYS.SYSFOREIGNKEYS s
INNER JOIN SYS.SYSCOLUMNS c1
ON ( s.fkeyid = c1.id and s.fkey = c1.colid )
INNER JOIN SYSCOLUMNS c2
ON ( s.rkeyid = c2.id and s.rkey = c2.colid )
WHERE OBJECT_NAME(fkeyid) = '
'


ORDER BY parent_table,child_table


Third Query: Show all child tables of a particular table

SELECT
OBJECT_NAME(rkeyid) parent_table,
OBJECT_NAME(fkeyid) child_table,
OBJECT_NAME(constid) fkey_name,
c1.name fkey_col,
c2.name ref_keycol
FROM SYS.SYSFOREIGNKEYS s
INNER JOIN SYS.SYSCOLUMNS c1
ON ( s.fkeyid = c1.id and s.fkey = c1.colid )
INNER JOIN SYSCOLUMNS c2
ON ( s.rkeyid = c2.id and s.rkey = c2.colid )
WHERE OBJECT_NAME(rkeyid) = '
'

ORDER BY parent_table,child_table


Reference:
1. Concept is taken from an online article, unfortunately don't have link

No comments: