Nice Script But With Some Problems
-
I have a master table with a lot of heirarchies. The script failed after 100 recursions.
-
i don't know what you mentioned
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
seems working fine for me
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] -
Try to use option (maxrecursion 0) as shown below
SELECT TableRelation,DependentTable
FROM cte
option (maxrecursion 0)
-
yes that would be the good option, but i don't think that it exceed 100 level, any case suggest me please very interesting to know about it what kind of structure it has,
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]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply