Binaryworld - How to script SQL tables using SQL DMO ... [ SQL Server -> DMO ]

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.

Click here to copy the following block

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

Click here to copy the following block

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

Click here to copy the following block

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