Click here to Skip to main content
12,943,701 members (58,866 online)
Click here to Skip to main content
Add your own
alternative version


32 bookmarked
Posted 17 Sep 2004

Database build tool

, 17 Sep 2004 CPOL
Rate this:
Please Sign up or sign in to vote.
An extensible tool to package and deploy database schema objects.


The database assembly build tool is a tool to allow a developer to select database schema items (tables, views, stored procedures, users etc.) and export the DDL to create these items to a SQL file. This file can then be run against another database to recreate the source schema. It is intended to be extensible to allow it to be adapted to any database platform, and a SQL server implementation is included in this article code to show this.


The DataSchemaBase is the "contract" between the data schema provider and the database build wizard. It sets out a set of interfaces that the data schema provider must implement in order that it can be used to generate the DDL.


The IDataSource class is the root of the data schema provider. It gives information about the capabilities of the data schema provider (does it support stored procedures, views etc.) and reads only collections of those database objects.

Public MustInherit Class IDataSourceBase

    Public MustOverride ReadOnly Property Indexes() As IIndexCollection
'...8< ....
    Public MustOverride ReadOnly Property SupportsViews() As Boolean
End Class


This defines what information the database wizard needs to know about a table - essentially, the fields that make up the table and the permissions that are applicable to that table.

#Region "Fields"
    '\\ --[Fields]---------------------------------------------------------
    '\\ The individual fields (aka columns) in this table
    '\\ -------------------------------------------------------------------
    Public MustOverride ReadOnly Property Fields() As IFieldCollection
#End Region

#Region "Permissions"
    '\\ --[Permissions]----------------------------------------------------
    '\\ The users or user groups that have permissions on this table
    '\\ -------------------------------------------------------------------
    Public MustOverride ReadOnly Property Permissions() As IPermissionCollection
#End Region

In addition, it provides a ReadOnly property DDL which is the SQL statement(s) that would be needed to recreate this table.


Each of the other database item types have a similar implementation class that is broadly like ITableBase.


This DLL contains a SQL Server specific implementation of the DataSchemaBase classes to allow you to extract the DDL for a SQL Server (or MSDE) database.


This implements the IDataSource interface for a SQL server database. Since this database platform supports all the database item types listed, it returns true for the SupportsStoredProcedures and related properties, and has to provide an implementation of the Storedprocedures and related object collections.


This type safe collection returns a list of all the tables in the selected database. It does this by querying the table sysobjects:

Public Class SQLServerTables
    Inherits ITableCollection
'..8< .....
    Public Sub New(ByVal dbConn As OleDb.OleDbConnection)
        '\\ Creates a collection of all the tables in the named database
        Const SQL_GETTABLES = "select name from sysobjects where type = 'U'"
    End Sub
End Class


This class returns the DDL required to create the specific table. It does this with a hard coded part (create table), and then iterates through the fields and permissions appending the DDL for them.

There are similar classes implementing each of the other database object types (view, stored procedure etc.)


This is a single form Windows application that uses the WizardControl from Divil to allow the developer to select and connect to a database, then select the objects to be extracted and lastly the target file name, then produce the DDL.

Plug-in data schema providers

The different types of data schema providers are read in from the application configuration file to allow you to add more providers without having to recompile the application.

   <!-- The plug in providors that implement the DataSchemabase classes 
        to write out the DDL for creating the database objects 
        on the target database platform 
        Key= The unique name that the providor is known by
        ImplementingAssembly = The assembly that holds the providor
        ImplementingClassType = The class that implements the providor
  <dataSchemaProvidor Key="SQL Server" 



Step 1: Connecting to the database

The first page in the wizard presents you with a textbox into which you put the ODBC connection string, and a drop down box to select the data schema provider. Once these are both filled in, it goes off and gets all the DB items from the database selected. This may take a minute or two on a large database schema.

Step 2: Selecting the objects to extract

The second step is to select the tables, views, users, stored procedures, triggers or user groups you want to extract. You need to have at least one object type selected before you can proceed to step 3.

Step 3: Selecting the output

Step 3 is to select a SQL file name to write the DDL to. If the file already exists, you will be prompted whether or not to delete it. You can then use the output file with the OSQL command line to generate the data schema on the target database.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Duncan Edwards Jones
Software Developer (Senior)
Ireland Ireland
C# / SQL Server developer
Microsoft MVP (Azure) 2017
Microsoft MVP (Visual Basic) 2006, 2007

You may also be interested in...


Comments and Discussions

GeneralI always got a 0 byte DDL file as output Pin
Ivn Loire2-Nov-06 22:47
memberIvn Loire2-Nov-06 22:47 
GeneralRe: I always got a 0 byte DDL file as output Pin
Duncan Edwards Jones7-Nov-06 3:46
memberDuncan Edwards Jones7-Nov-06 3:46 
QuestionUpdating Schema? Pin
Brad1-Dec-04 12:59
memberBrad1-Dec-04 12:59 
AnswerRe: Updating Schema? Pin
Merrion5-Jan-05 23:35
memberMerrion5-Jan-05 23:35 
GeneralRe: Updating Schema? Pin
Brad7-Jan-05 9:32
memberBrad7-Jan-05 9:32 
GeneralRe: Updating Schema? Pin
Merrion23-Jan-05 23:20
memberMerrion23-Jan-05 23:20 
QuestionSuggestions for impreovements? Pin
Merrion22-Sep-04 2:32
memberMerrion22-Sep-04 2:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170518.1 | Last Updated 17 Sep 2004
Article Copyright 2004 by Duncan Edwards Jones
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid