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
2019-04-10