Click here to Skip to main content
Click here to Skip to main content

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

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.

//
// 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.

//
// 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.

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

Share

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 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 PinmemberMember 87144215-Dec-13 8:58 
Questionhow do it with C# ? PinmemberN3CAT120-Jul-07 23:24 
GeneralRequired files PinmemberTom Wright30-Jul-04 6:00 
Generalc++ PinsussKevin Smith1-Mar-04 11:27 
GeneralRe: c++ Pinmembersammyc22-Jun-04 2:33 
GeneralRe: c++ PinmemberKevin Smith22-Jun-04 3:16 
GeneralAbout Error of SQL DMO Pinmemberkriegsmar15-Apr-03 10:52 
GeneralRe: About Error of SQL DMO PinmemberVinod Kumar M15-Apr-03 21:10 
GeneralRe: About Error of SQL DMO Pinmemberkriegsmar26-Apr-03 17:24 
GeneralRe: About Error of SQL DMO PinmemberVinod Kumar M28-Apr-03 21:25 
Hello,
 
I've wrote this on VB 6.0 SP5 Version. I've tested this on Win 2K clients. Not yet on a XP client. I've seen many times that XP requires a different pre-requisite. And Iam not sure what XP requires here?Confused | :confused:
 

 
HTH,
Vinod Kumar
MCSE, DBA, MCSA
GeneralRe: About Error of SQL DMO PinmemberPaulT16-Feb-04 3:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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.141030.1 | Last Updated 13 Apr 2003
Article Copyright 2003 by Vinod Kumar M
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid