well can you explain the scenario please because see the following code

USE tempdb

GO

CREATE TABLE [dbo].[EmpTbl](

[Employeeid] [int] NOT NULL,

[EmployeeName] [varchar](50) NULL,

[Department] [varbinary](50) NULL,

[Supervisorid] [int] NULL,

CONSTRAINT [PK_EmpTbl] PRIMARY KEY CLUSTERED

(

[Employeeid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[EmpTbl] WITH CHECK ADD CONSTRAINT [FK_EmpTbl_EmpTbl] FOREIGN KEY([Supervisorid])

REFERENCES [dbo].[EmpTbl] ([Employeeid])

GO

ALTER TABLE [dbo].[EmpTbl] CHECK CONSTRAINT [FK_EmpTbl_EmpTbl]

GO

DECLARE @TableName AS VARCHAR(250)='EmpTbl'

;WITH cte AS

(

SELECT cast(OBJECT_NAME (fkc.parent_object_id) as VARCHAR(MAX)) AS TableRelation, OBJECT_NAME(fkc.parent_object_id) AS DependentTable, fkc.parent_object_id AS

childID, 1 AS ReLevel

FROM sys.foreign_key_columns fkc

WHERE fkc.referenced_object_id = OBJECT_ID (@TableName)

UNION ALL

SELECT cast(c.TableRelation +'-->'+ OBJECT_NAME (fkc.parent_object_id) AS VARCHAR(MAX)) AS TableRelation, OBJECT_NAME(fkc.parent_object_id) AS DependentTable, fkc.parent_object_id AS

childID, c.ReLevel + 1

FROM sys.foreign_key_columns fkc

INNER JOIN cte c

ON fkc.referenced_object_id = c.Childid AND fkc.parent_object_id<>c.childid

)

SELECT TableRelation,DependentTable

FROM cte

DROP TABLE EmpTbl

it only return one row

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]