Jan 5, 2014

Write a sql query to get all the parent and child tables of a given table in sql server


How to write a sql query or script to get or list out all the parent and child tables name of a given or specified table in sql server
  
--Change the table name
DECLARE @TableName AS VARCHAR(100) = 'tblEmployee'

SELECT
OBJECT_NAME(fk.referenced_object_id) AS TableName,
'Parent' AS Relation
FROM sys.tables t INNER JOIN sys.foreign_keys fk
ON t.object_id = fk.parent_object_id
WHERE t.name = @TableName

UNION ALL

SELECT
OBJECT_NAME(fk.parent_object_id),
'Child'
FROM sys.tables t INNER JOIN sys.foreign_keys fk
ON t.object_id = fk.referenced_object_id
WHERE t.name = @TableName

No comments:

Post a Comment