How can I script to create an MS SQL Server database with proper object ordering?

Question

I am trying to create a MS SQL Server 2005 database script for a single file. So far, I have been trying to use SQL Management Studio and the MS Database Publishing Wizard.

Both tools will script database objects without problems, although none of the tools are scripting objects in the correct order of creation. For example, a script may have a script view that requires a table that will not be created until it is below the row.

Do you know how to script a database schema with the correct order to create an object?

Answer 1

We use this to create db scripts, I will need to run it again to make sure it creates dependent objects first ... but I haven't had a problem yet. http://www.red-gate.com/products/SQL_Compare/index.htm

Answer 2

My answer will be a bit complicated, and it only works if you script your entire database (i.e. save everything in SQL scripts). What we did in the massive project was to organize the scripts in the following structure:

• _ddl : contains table changes, such as new columns, indexes, relations, etc.
• _fn : saved functions
• _prc : stored procedures
• _static : obviously, "static data" or data that should be in the database after deployment.
• _tab : tables (scripting)
• _trg : triggers
• _views : view scripts

Folder names are, of course, our random choice, you can always organize things differently. After that, we created a batch script package to merge all these files into one SQL in the following order:

• _tab
• _ddl
• _trg
• _views
• _fn
• _prc
• _static

The key trick is to write your scripts the way they can be run a thousand times. This means: drop your procedures before creating them, check if the table exists before creating it, check if the row exists before adding it to static, etc.

This is not perfect, but he is doing his job.

Answer 3

There is a naive but surprisingly effective way to solve the ordering problem: keep running each script. An individual script will either work (after running other scripts) or crash> scripts # (in this case, this is a bad script). You might find a bad script faster / easier, but I never needed it.

If you have 1 gigantic script, it will probably be shared by GO statements. This should be enough to run it as many times as there are unique GO statements. Any attempts to create an object that already exists will fail and interrupt the package. The next batch will run smoothly. In the end, you have the necessary objects created - and re-playing the entire script will create a dependent object (and the already created independent objects will crash). However, you will never get a script to run without errors.

If you want to get a little involved, you can split the giant script into separate lots and run them individually. Now you can track what order is needed to make them work. Just recompile them in that order and output a new script. It should work without errors.

Or spend $ 500 on a tool that already does this (RedGate, Visual Studio Ultimate / Database Edition, etc.).

Answer 4

Lecter has a good approach. You can combine these scripts with a powershell script (or another language)

run the script:

PS builddir:\> .\buildsql.ps1 -currentbuilddir "C:\Documents and Settings\sam\My Documents\svn\ticketing" -buildfile "sqlbuild.sql" -teardownfile "teardown.sql" 

powershell script:

 param($currentbuilddir,$buildfile1,$teardownfile) new-psdrive -name builddir -PSProvider filesystem -Root (resolve-path $currentbuilddir) cd builddir: rm $buildfile1 rm $teardownfile Get-item Scripts_Build_1* | ForEAch-object {cat $_ >> $buildfile1; "GO --SYSTEM INSERTED GO--------------" >> $buildfile1} Get-item Scripts_Build_3* | ForEAch-object {cat $_ >> $teardownfile; "GO --SYSTEM INSERTED GO------------" >> $teardownfile} 

Here I delete the build file and tear down the files - I actually worked on using my own sql encryption in the database, so detachment may not be applicable. I had all my scripts in the same directory, so you may need to modify this script to do some recursion.

Answer 5

You can try the xSQL object at http://www.xsqlsoftware.com/Product/Sql_Schema_Compare.aspx This worked great for me. the script is created in the correct order whenever possible (there are times when the generated script cannot be executed directly, but in most cases it works)