_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › 譬焔覯襦企覲企覦覯


WITH myTableList AS
(
	SELECT OBJECT_ID, name, schema_name(schema_id) sch_name
	FROM sys.tables
	--where name like 'stuffICareAbout%'
),
myForeignKeyList AS
(
	SELECT fk.parent_object_id, fk.referenced_object_id
	FROM sys.foreign_keys fk
	join myTableList mtl_parent
		ON mtl_parent.OBJECT_ID = fk.parent_object_id
	join myTableList mtl_referenced
		ON mtl_referenced.OBJECT_ID = fk.referenced_object_id
),
TablesDependencies AS
(
	SELECT name AS TableName,
		OBJECT_ID AS TableID,
		sch_name,
		0 AS LEVEL
	FROM myTableList 
 
	UNION ALL
 
	SELECT mtl.name, mtl.OBJECT_ID, mtl.sch_name, td.LEVEL + 1
	FROM myTableList mtl
	JOIN myForeignKeyList mfk
		ON mfk.parent_object_id = mtl.OBJECT_ID
		AND NOT mfk.parent_object_id = mfk.referenced_object_id
	JOIN TablesDependencies td
		ON mfk.referenced_object_id = td.TableID
)
SELECT sch_name, TableName, MAX(LEVEL) AS LEVEL
FROM TablesDependencies
GROUP BY sch_name, TableName
ORDER BY LEVEL, TableName

蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:53

朱 蠏瑚唄 . (れ M. ろ襴)