SDU Tools: CalculateTableLoadingOrder – follow table dependencies in T-SQL

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

 

Leave a Reply

Your email address will not be published.