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

참고: http://michaeljswart.com/?p=629&utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+TheDatabaseWhisperer+%28Michael+J.+Swart%2C+Database+Whisperer%29