It is a good practice to store creation scripts for the database organized in a way that there is a single SQL file for each database object. The problem starts when one wants a single script that creates all of these database objects.

I’ve seen a solution in which the creation SQL files are concatenated, and then executed. Obviously the first time some dependencies were not right, so the script failed. This can occur for many reasons, e.g. a foreign key creation is executed before the referenced table is created. So the next step is to look at the error message, and reorder the problematic object. One executes this script, and looks at the error message again. Finally there will be a script that executes without a problem. Seemingly. One can create a stored procedure that references another, before the referenced stored procedure is created. So for example, if the stored procedure ProcA is calling ProcB, one can create ProcA before ProcB is created. Assuming that ProcB is created afterwards, there is no difference in terms of the way ProcA is executed. However, the sysdepends table will not be updated properly.

SQL Compare 6.0 Professional will provide a solution to the above task. It can read in creation SQL files from a folder (the final version will read in recursively), and compare the scripts against a live database. If you compare these scripts against an empty live database, you can use the synchronize wizard to create a script that orders the object in the correct dependency order.

There are many options that can influence how this ueber script is assembled. First, you can use “Do not use transactions in synchronization SQL scripts“. If this option is set, SQL Compare will basically just concatenate the creation SQL files in the right dependency order. If this option is not set, then transactions are added to ensure that the whole script is executed as a single transaction.

Another useful way to influence the generated script is to compare the creation script files against a specific version of SQL Server. If the empty database is on SQL Server 2005, then the generated script will also use SQL Server 2005 syntax when it is available.

SQL Compare 6 processes the scripts that it reads in, as it is seen in the above example, when SQL Compare can decide on the syntax to use. If one wants to just see a list of database objects in the order they should be executed, one can look at the action plan SQL Compare generates, and order the scripts manually, but with a bit of “help”.

While ordering SQL creation scripts is not the primary use of SQL Compare, it is a powerful means to create a single SQL creation script from a large number of SQL files.