The program is a simple C# program which uses SQL-SMO to script out each database object

to its own file. It steps through each object type [table, view, procedure, etc.] and

creates the appropriate folders as it goes. The folder structure is exactly the same as

that produced by the Data Dude  let's just adopt Microsoft's tree and file

naming convention as a standard and not try to invent a new one.

See "FilesystemTree.txt".

The program is a console application [.exe] which requires 3 parameters:

• The Server name
• The Database name
• The Root Path to store the files 

  the Server name and Database name will be created as sub-folders under this path

Optionally, for users who cannot logon via Windows Authentication, the Username and

Password may be supplied as the 4th and 5th parameters.

The entire source code is available on the SourceForge web site, but here are a couple of

highlights. After validating the parameters, it creates an SMO scripter object which contains

the scripting options. Most object types use the same settings, so these are established up

front  see "Common Scripter.txt." It then simply steps through each object type

and iterates through the SMO collection of that type and sets additional, appropriate

scripting parameters including the filename. For each object it then calls the

simple "ScriptIt" method which writes out the file. See "ScriptIt.txt". An

example of how it iterates through each object type is shown for Stored Procedures

in "StoredProcedures.txt."

Files, source management, project deployment

Scripting out a database in the manner proscribed by the Data Dude creates dozens,

even hundreds of files for each database. What do you do with all these files? At a bare

minimum you would produce these files periodically just to make sure you have your databases

documented.

However, a better plan is to put them in a source management system such as Visual SourceSafe.

Unless you use source management, "the truth" resides only in the production

databases -- and what "the truth" was in days gone by is unknown -- except in backup

files. What an object looked like last week is not tracked. But for every type of development

other than database development, "the truth" has always resided in the source

management system. Source management tracks the who/when/why each object was changed, and

provides a means of labeling sets of files into a project [i.e., a "release"].

Plan A, therefore, is to take these files and check them into the source management system.

From that point forward, development follows the widely accepted checkout/modify/check-in

paradigm. Deployment of a project to the test database and eventually to the production

database must be done using the versions of the files modified for the project

obtained from the source management system, typically using a "get

latest".

So, for example, a CREATE PROCEDURE file originally produced by the tool and now residing

in source management is checked out. The predicate is changed to ALTER PROCEDURE and the

modifications are made and tested. The file is then checked back in to source management --

and the who/when/why are recorded. Some object types don't have an ALTER predicate,

so for these the file is modified to have both the DROP and the CREATE statement. This

tried-an-true source control methodology has the additional benefit of allowing only one

developer at a time to work on each object -- because the source management system

won't let the second developer check the file out until the first developer checks it

back in [usually after the release is deployed].

Tables, of course, contain data so dropping and recreating a table is inconvenient at best.

For tables I suggest that each table can have up to 4 supplemental files, each with a

specific name. To review, each table was given its own file containing the CREATE TABLE

statement with a naming pattern of:

   <schema>.<name>.table.sql

Additions or changes to the table's indexes, triggers and constraints are made by

modifying the separate, individual files containing those child objects. But you need to control

other actions on tables [e.g., for lookup tables the project may need to insert, update

or delete values]. Of course, you often want to add a column or two to the table. And, again,

you need to be able to prevent multiple developers from modifying the table concurrently. So

adopt a standard such as the following for naming a table's supplemental files:

   <schema>.<name>.table.alter.sql
   <schema>.<name>.table.insert.sql
   <schema>.<name>.table.update.sql
   <schema>.<name>.table.delete.sql

Once a table is modified and the <schema>.<name>.table.alter.sql

file is created, this same file would be reused for each ensuing alteration. All changes for

a given deployment for an individual table would be in this file in an ALTER TABLE statement.

Thus, these changes get tracked from deployment to deployment, and the current table's

structure can be recreated from scratch by redeploying the original CREATE TABLE file and

all deployed versions of the ALTER file.

The project includes a command file which automates deployment of the files in a

release. Called "Deploy.cmd", it iterates through the file tree in the

appropriate sequence and executes each file via osql.exe. It takes as arguments:

• the path of the files [where you put them when you did

  the "get latest" for the current release]

• the target server
• the target database
• optionally, a flag which indicates that you want the files

  deleted after being processed

That's all there is to it. Script out your databases and begin using source management

to control your database development.

SourceForge

The SourceForge web site [

http://sourceforge.net/projects/script-sql-db/

] has a convenient way to report bugs, ask questions, submit suggestions, etc., and all are

welcome. The enhancement which allows a user to connect via Username and Password came from

just such a request entered in the project's Open Discussion Forum.

You can even register to "monitor" the project -- it you do this you will

be notified by email whenever an update to the project occurs. [The project admins do not

know who is on the list -- just that one or more people have registered.]

Of course, you will have the source code so if you want to make your own changes you are

free to do so.