creating a master ddl

Question

I am currently creating a master ddl for our database. Historically we have used backup/restore to version our database, and not maintained any ddl scripts. The schema is quite large.

My current thinking:

Break script into parts (possibly in separate scripts):

table creation
add indexes
add triggers
add constraints
Each script would get called by the master script.

I might need a script to drop constraints temporarily for testing
There may be orphaned tables in the schema, I plan to identify suspect tables.
Any other advice?

Answer 1

That's okay. Running it as SQLCMD script or in SSMS using SQLCMD mode (query menu).

Answer 2

Hi Kevin,

The nice thing about building all the tables first and then building all the constraints, is that the tables can be created in any order.  When I've done this I had one file per table, which I put in a directory called "Tables" and then a script which executed all the files in that directory.  Likewise I had a folder for constraint scripts (which did foreign key and indexes too), which were executed when after the tables were built.

I would separate the build of the triggers and stored procedures, and run these last.  The point about these is they can be run and re-run on the database without affecting the data.  This means you can treat them just like ordinary code.  You should include "if exists...drop" statements at the beginning of each trigger and procedure script, to make them re-runnable.

So the order would be

1. table creation
2. add indexes
3. add constraints

Then

1. add triggers
2. add stored procedures

On my current project we are using MSBuild to run the scripts.  There are some extension targets that you can get for it which allow you to call sql scripts.  In the past I have used perl which was fine too (and batch files...which I would not recommend - the're too limited).

---

Iric Wen
TechNet Community Support

Answer 3

Hi kevin gray123,

This is good approach but if you want a single single script that can handle all these script creation

you can use VisualStuadio 2010.

below attached is the screen for how you can create script which be used to create schema of your old db