Click here to Skip to main content
11,934,214 members (39,520 online)
Click here to Skip to main content
Add your own
alternative version


28 bookmarked

Using SQL DMO to Script Tables

, 12 Apr 2003
Rate this:
Please Sign up or sign in to vote.
This article shows the power of using SQL-DMO with SQL Server 2000 to script out objects. In this example we would script out tables.

Sample Image - DemoPicture.jpg


SQL-DMO encapsulates SQL Server components and presents them as attributes of the component piece to you as properties of an object instance. You would have to alter the properties of the instance or use object methods to automate SQL Server administration. This article takes a preview into one such capability of DMO to script out table description from SQL Server. This code helps you script the table definitions from the database you choose. You have an option to script out Indexes and Triggers associated with the tables.

Application pre-requisite

SQL-DMO uses the Microsoft® SQL Server ODBC driver to connect to and communicate with instances of SQL Server. If you have installed SQL Server Enterprise Manager then you should have the SQL DMO installed on your system. If you don't have the SQL Tools then the following article shows you how to install these objects.

MDAC v2.6 or higher is required for this application. This can be obtained from Microsoft. This contains the OLE DB drivers that are required to hook up to the database or the data source.

Using the application

This application first enumerates the list of all databases in the current network using DMO APIs. You can plan to choose any of the SQL Servers enlisted and provide a credential to connect. In the event of any failures an error is shown to the user.

// Enumerating all the SQL Servers in the network
    Set NameList = sqlApp.ListAvailableSQLServers
    ' Get the Servers on the network and display the Values into the combo
    For index = 1 To NameList.Count
        cboServer.AddItem NameList.Item(index)

After the user is successfully logged into the SQL Server machine, the next step we perform is to enumerate all the databases in the SQL Server selected in the earlier step. On selecting a database, we query and get all the tables associated with this database. We have also added provision to select all the tables with a (all) keyword in the selected list.

// Enumerating all the Databases on the selected Server.
    ' Fill the database combobox
    For Each db In SQLServer.Databases
        If Not db.SystemObject Then
            cboDatabases.AddItem db.Name
        End If

We have just touched the surface. The actual functionality is yet to come. Yes, the scripting process. On selecting the database and the table to script, the user is just one click away in getting his scripts out. On pressing the Generate Script button, the user would get another window with the scripts for the particular table.

// Generating the table scripts
        Set tbl = SQLDatabase.Tables(cboTables.Text)
        Script = Script & tbl.Script(param)


We have just touched the surface in exploring what SQL-DMO can offer us. This surely is an easy and elegant way to code. For more information on SQL-DMO functionality refer to MSDN or SQL Server BOL.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

I did my Bachelors of Engineering in Mechanical from Anna University, Chennai. After graduation, I joined SCT Software Solutions. I have been working on Microsoft Technologies for the past three years.

Catch all the resources and articles I have written at, the site I co-hosts. He has been very passionate about SQL Server programming and loves to experiment different programming tricks in SQL Server 2000.

I am an MVP in SQL Server 2000.

Web Site:

You may also be interested in...

Comments and Discussions

Questionsource code Pin
Member 87144215-Dec-13 9:58
memberMember 87144215-Dec-13 9:58 
Questionhow do it with C# ? Pin
N3CAT121-Jul-07 0:24
memberN3CAT121-Jul-07 0:24 
GeneralRequired files Pin
Tom Wright30-Jul-04 7:00
memberTom Wright30-Jul-04 7:00 
Generalc++ Pin
Kevin Smith1-Mar-04 12:27
sussKevin Smith1-Mar-04 12:27 
GeneralRe: c++ Pin
sammyc22-Jun-04 3:33
membersammyc22-Jun-04 3:33 
GeneralRe: c++ Pin
Kevin Smith22-Jun-04 4:16
memberKevin Smith22-Jun-04 4:16 
GeneralAbout Error of SQL DMO Pin
kriegsmar15-Apr-03 11:52
memberkriegsmar15-Apr-03 11:52 
GeneralRe: About Error of SQL DMO Pin
Vinod Kumar M15-Apr-03 22:10
memberVinod Kumar M15-Apr-03 22:10 
GeneralRe: About Error of SQL DMO Pin
kriegsmar26-Apr-03 18:24
memberkriegsmar26-Apr-03 18:24 
GeneralRe: About Error of SQL DMO Pin
Vinod Kumar M28-Apr-03 22:25
memberVinod Kumar M28-Apr-03 22:25 
GeneralRe: About Error of SQL DMO Pin
PaulT16-Feb-04 4:40
memberPaulT16-Feb-04 4:40 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.151126.1 | Last Updated 13 Apr 2003
Article Copyright 2003 by Vinod Kumar M
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid