FINDING HEIRCHYOF TABLE DEPENDENCY

Select tc1.TABLE_NAME, COUNT(*) AS RANK
INTO #TABLE_RANK
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC1
GROUP BY TABLE_NAME
HAVING COUNT(*) =1
--sELECT * FROM #TABLE_RANK ORDER BY RANK,TABLE_NAME
--sELECT Table_name,max(Rank) as Rank FROM #TABLE_RANK Group by table_name ORDER BY RANK,TABLE_NAME
DECLARE @RANK INT;
SET @RANK=1;

WHILE((SELECT COUNT(DISTINCT TABLE_NAME) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS)>(sELECT COUNT(DISTINCT TABLE_NAME) FROM #TABLE_RANK))
--SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME NOT IN (sELECT TABLE_NAME FROM #TABLE_RANK) )
BEGIN

IF (@RANK>13) BREAK;


INSERT INTO #TABLE_RANK(TABLE_NAME,RANK)
select TABLE_NAME, @RANK+1 as rank from INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
where TC.CONSTRAINT_NAME in
(
Select CONSTRAINT_NAME from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
where UNIQUE_CONSTRAINT_NAME in
(
select CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
where CONSTRAINT_TYPE='Primary KEY'
and table_name in
(
Select tc1.TABLE_NAME from #TABLE_RANK TC1 WHERE RANK=@RANK
)
)
)
GROUP BY TABLE_NAME

SET @RANK=@RANK+1
END

SELECT Table_name,max(Rank) as Rank FROM #TABLE_RANK Group by table_name ORDER BY RANK,TABLE_NAME