Awesome article and very helpful!!! I haven't had a chance to go through the code yet, but the description of what you are trying to accomplish is very clear.
I'm excited because I think I might be able to use this code in the future. If I am making major changes to a database, I will completely re-engineer the database from scratch for both development purposes and later as part of the rollout process. What I mean by "completely re-engineer..." is that I follow these steps (which happens about 1 or 2 times a year for the major databases):
. a) create new database in SSMS
. b) generate the new database schema from ERwin, the tool I use to store the datamodel and which can be used to add all the tables, indexes, foreign keys etc.
. c) run a script that copies all the data from the existing database into the new one with the new schema just created.
We have a custom, home-grown application which creates the script for step c). But that custom application requires that we generate a file from ERwin that will determine the correct order for the tables to do the copying.
I've always wondered how we could take that ERwin file out of the picture and figure out the correct table order using SQL. It is a much cleaner solution for a couple of reasons. For example, one time when ERwin released an "upgrade", the format of the report/file that we use got changed. So, we had to change the code in our custom app. It would be better to not have to rely on ERwin's table generating file.
Thanks for the idea! I hope I will be able to use it in the future at some point.