How to programmatically retrieve the list of tables from SQL Server in dependency order?

Question

I am working with SQL Server 2008 R2. I want to query a database and retrieve all user defined tables (easy enough, SELECT Name FROM sys.tables), but I want it to be ordered so that if insert statements were executed in that order, they would not run into foreign key constraints.

In other words, I want the list of tables sorted by dependencies so that the highest level tables (i.e. the ones that had no dependencies) are before those that rely on them.

What is the most efficient way to query this?

Thanks for any help.

You're building a lot of infrastructure when you could not allow user defined tables (nevermind constraints). Another alternative to consider would be EAV — OMG Ponies
– OMG Ponies, Commented Nov 11, 2011 at 18:47

Ben Gribaudo · Accepted Answer · 2011-11-11 18:53:01Z

3

Does the ''top-down hierarchy'' query discussed on http://www.jasinskionline.com/technicalwiki/Default.aspx?Page=List-Tables-in-Dependency-Order-SQL-Server&NS=&AspxAutoDetectCookieSupport=1 produce the list you're looking for?

answered Nov 11, 2011 at 18:53

Ben Gribaudo

5,1771 gold badge43 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TomTom · Accepted Answer · 2011-11-11 19:28:54Z

0

Are you sure it CAN be done? It is legal in SQL to have circular references, so it is quite commong that there is no perfect order. Optional null values allow inserts (followed by an update). But tree relations are not a uni directional graph in most scenarios.

answered Nov 11, 2011 at 19:28

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Collectives™ on Stack Overflow

How to programmatically retrieve the list of tables from SQL Server in dependency order?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related