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

Question

I'm trying to script an MS SQL Server 2005 database to a single file. So far I've tried using SQL Management Studio and the MS Database Publishing Wizard.

Both tools will script the database objects without a problem although neither tool is scripting objects in the right order of creation. For instance the script might script a view which requires a table that's not going to be created until later down the line.

Do you know how to script the database schema with the proper order of object creation?

Answer 1

We use this to create the db scripts, i'd have to run it up again to ensure it creates objects dependants 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 works only if you script your entire database (i.e. keep everything in SQL scripts). What we did in a massive project was organize the scripts into the following structure:

• _ddl: holds the table changes, such as new columns, indexes, relations etc..
• _fn: stored functions
• _prc: stored procedures
• _static: obviously, "static data", or data which has to be in the database upon deployment
• _tab: tables (create scripts)
• _trg: triggers
• _views: view scripts

Folder names are of course our random choice, you can always arrange things differently. After that, we've created a batch script to merge all those files in a single SQL in the following order:

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

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

It's not perfect, but it gets the job done.

Answer 3

Lecter has a good approach. You can concatenate these scripts using a powershell script (or other 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 am deleting a build file and tear down files - I was actually working on applying sql native encryption to a database, so the teardown may not be applicable. I had all my scripts in one directory, so you might have to alter this script to do some recursion, I would think.

Answer 4

You can try xSQL Object at http://www.xsqlsoftware.com/Product/Sql_Schema_Compare.aspx It has worked great for me. The script is created in the correct order whenever it's possible (there are cases where the generated script cannot be executed directly, but in most cases it works)