Automatically determine dependencies from a collection of table Object script files

I have a folder /Tables that contain all the table objects scripts. Some of these table objects have dependency between each other hence the need for the script to be executed in order. For now we have to edit our bat file if depende开发者_C百科ncy collision occur and fix it in the order needed. Since every morning we have a push using OSQL cmd line switch to push these table to a staging database server. Is there a way to automate the order of the table objects scripts by dynamically?

SQL Compare Pro will probably be able to do this if you specify your folder of tables as a scripts folder source database. Point it to your target (which could be an empty database) and run through the Synchronization Wizard to generate a script in dependency order.

It's going to be very difficult to determine dependencies from the script files. You'd have to parse everything and keep track of the dependencies. Another approach, if you have a single object per file (i.e. one table, view, etc.), is to keep track of which files have not been executed successfully, and loop through the list until it's empty. Depending on how deeply nested your dependencies are, you might have to make lots of passes, but chances are good that the list will get short very quickly.

Found another approach which is to use Visual Studio 2010 database project. You can import any user defined scripts or folders having multiple scripts into Visual Studio 2010 database projects. Dependencies are compiled by the visual studio and deployment can be made to multiple databases.