CODEWITHSUNDEEP
sql-serversql-server-2008
ca9163d9
ca9163d9

Reputation: 29179

Sort the tables in the order of FK references?

How to sort a list of tables in the order of FK dependency with the least depended tables on top?

For example, table A has a FK references B, which has FK references C. The result should be

    C
    B
    A

select * from sys.tables order by .....?

Upvotes: 2

Views: 106

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

In general case you can have multiple graphs of dependencies with loops. I would not try to do it in SQL. It is a rather difficult problem.

1) Extract all pairs of parent - child tables from sys.tables and make a simple long list of all dependencies.

2) Use something like "dot" from http://www.graphviz.org/ to process these pairs and generate a graph.

I personally use this approach to visualize graph of dependencies between salespeople that are stored in my database.

Out of curiosity I did it for all foreign keys in my database. Each node here is a table.

keys

You can't see all details on this screenshot, but it is enough to get the idea. I can now easily find tables that are at the top and bottom of the dependency chain (they are in the left-most and right-most columns on the graph).

I used this query:

select 
    '"'+ParentTables.name+'" -> "'+ReferencedTables.name+'";' AS dot
from
    sys.foreign_keys
    inner join sys.tables AS ParentTables ON ParentTables.object_id = sys.foreign_keys.parent_object_id
    inner join sys.tables AS ReferencedTables ON ReferencedTables.object_id = sys.foreign_keys.referenced_object_id

Put results in a text file keys.txt, first line of the text file is:

digraph G { rankdir=LR

last line of the text file is

}

Command line to generate the graph:

"C:\Program Files (x86)\Graphviz2.38\bin\dot.exe" -okeys_graph_20150117.png -Tpng keys.txt

Upvotes: 1

Spock
Spock

Reputation: 4910

Try this...

select a.Name
from sys.tables a
    LEFT JOIN sys.foreign_keys b ON a.object_id = b.parent_object_id
GROUP BY a.Name
ORDER BY COUNT(DISTINCT b.name) ASC

Upvotes: 1

Related Questions