Michael J. Swart

March 26, 2010

Listing Tables Ordered By Dependency

Filed under: SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 4:47 pm

Short post for the Weekend.

There’s a number of use cases that may require you to see a list of database tables ordered by dependency.

For example, you may want to know in which order to create tables if you’re dealing with a scripting project. (On the flip side, if you’re looking to delete tables, reverse the order).

I came up with a pretty script that lists the tables you care about in order of how they can be created (i.e. create tables at level zero before moving on to level one tables).

WITH myTableList AS
	select object_id, 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,
		0 AS level
	FROM myTableList 
	SELECT mtl.name, mtl.object_id, 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 TableName, MAX(level) as level
FROM TablesDependencies
GROUP BY TableName
ORDER BY level, TableName

Modify this to suit your needs, i.e:

  • Modify the where clause to filter only the tables you care about.
  • Modify the fields to report on schema if it’s something you care about


No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress