Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server
Article

Using SQL DMO to Script Tables

Rate me:
Please Sign up or sign in to vote.
4.41/5 (14 votes)
12 Apr 20032 min read 88K   2.6K   28   11
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

Introduction

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.

VB
//
// 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)
    Next

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.

VB
//
// 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
    Next

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.

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

Conclusion

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.

License

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


Written By
India India
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 http://www.ExtremeExperts.com, 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: http://www.ExtremeExperts.com

Comments and Discussions

 
Questionsource code Pin
Member 87144215-Dec-13 8:58
Member 87144215-Dec-13 8:58 
Questionhow do it with C# ? Pin
N3CAT120-Jul-07 23:24
N3CAT120-Jul-07 23:24 
GeneralRequired files Pin
Tom Wright30-Jul-04 6:00
Tom Wright30-Jul-04 6:00 
Generalc++ Pin
Kevin Smith1-Mar-04 11:27
Kevin Smith1-Mar-04 11:27 
GeneralRe: c++ Pin
S.Cartwright22-Jun-04 2:33
S.Cartwright22-Jun-04 2:33 
GeneralRe: c++ Pin
Kevin Smith22-Jun-04 3:16
Kevin Smith22-Jun-04 3:16 
GeneralAbout Error of SQL DMO Pin
kriegsmar15-Apr-03 10:52
kriegsmar15-Apr-03 10:52 
GeneralRe: About Error of SQL DMO Pin
Vinod Kumar M15-Apr-03 21:10
Vinod Kumar M15-Apr-03 21:10 
GeneralRe: About Error of SQL DMO Pin
kriegsmar26-Apr-03 17:24
kriegsmar26-Apr-03 17:24 
GeneralRe: About Error of SQL DMO Pin
Vinod Kumar M28-Apr-03 21:25
Vinod Kumar M28-Apr-03 21:25 
GeneralRe: About Error of SQL DMO Pin
Paul Talbot16-Feb-04 3:40
Paul Talbot16-Feb-04 3: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.