it seems if there is a circular dependency in your Database then this script is not working to avoid the circular dependency

use the following condition with the inner join of the recursive part

AND c.TableRelation not like c.DependentTable +'-->%'

Here is an Example

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*/

Create Table [dbo].[ManagerTbl](

[Managerid] [int] NOT NULL,

[EmployeeName] [varchar](50) NULL,

[AsstManagerid] [int] NOT NULL,

CONSTRAINT [PK_Managerbl] PRIMARY KEY CLUSTERED

(

[Managerid] ASC

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

)

GO

ALTER TABLE [dbo].[ManagerTbl] WITH CHECK ADD CONSTRAINT [FK_ManagerTbl_EmpTbl] FOREIGN KEY([Managerid])

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

GO

ALTER TABLE [dbo].[ManagerTbl] WITH CHECK ADD CONSTRAINT [FK_ManagerTbl_EmpTbl_1] FOREIGN KEY([AsstManagerid])

REFERENCES [dbo].[ManagerTbl] ([Managerid])

GO

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

REFERENCES [dbo].[ManagerTbl] ([Managerid])

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

AND c.TableRelation not like c.DependentTable +'-->%'

)

SELECT *

FROM cte

a

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]