If there is a reasonable number of tables in a SQL Server database, and I've also got foreign keys linking them, it can be difficult to work out the order of the dependencies. That's a hassle when I want to load data, and for other admin functions that I might need to perform. So we've fixed that.
In our free SDU Tools for developers and DBAs, there's a stored procedure called CalculateTableLoadingOrder.
It takes one parameter: the @DatabaseName.
The procedure works out all the dependencies and then lists the tables in order. It shows the following:
LoadOrder – the overall order
LoadingPhase – tables in the same phase have no dependency on each other and could be loaded concurrently
SchemaName – name of the schema
TableName – the name of the table
TableObjectID – the object_id for the table
IsSystemTemporal – is this a temporal table (we may need to worry about history)
IsTemporalHistory – is this the history table for a temporal table (and if so, the TemporalHistorySchemaName and TemporalHistoryTableName tell you which table)
You can see this procedure in action in the main image above, and in this video:
To become an SDU Insider and to get our free tools and eBooks, please just visit here:
http://sdutools.sqldownunder.com