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 UNION ALL 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
Cheers
