Introduction to SQL-DMO

... and much more.

The fun happens inside sqldmo.dll which sits in the \Tools\Binn directory under your Microsoft SQL Server installation. As it is a COM object, you can use it in any COM friendly platform. This includes C++, VB, WSH, ASP, Delphi and of course .NET.

For the purposes of this article, I am going to give examples in Visual Basic 6. These examples will work with very little modification in VB Script, including ASP.

The first step is to connect to a database server. You can do this using SQL authentication, or use the built in NT authentication depending on your application.

Connecting using SQL Authentication

Dim objDMO as SQLServer
Set objDMO = new SQLDMO.SQLServer
objDMO.Connect "(local)", "sa", "of_course_I_changed_my_password"

Connecting using NT Authentication

This is as simple as setting the "loginsecure" property to True and leaving off the login and password.

Dim objDMO as SQLServer
Set objDMO = new SQLDMO.SQLServer
objDMO.loginsecure = true
objDMO.Connect "(local)"

Don't forget at the end of this you will want to disconnect and clean up your objects with a simple :

objDMO.DisConnect
Set objDMO = nothing

The Tables Collection

The object model within DMO is very similar to the heirachy in Enterprise Manager. Underneath each server is a collection of databases. Each database has collections of objects. Tables, Stored Procedures and Views all exist in collections under a database.

Here is an example of some code that would loop through the tables collection to return the name of each table.

'Assuming, we have set up our connection.

Dim objDB As Database
Set objDB = objDMO.Databases("northwind")

Dim oTable As Table
For Each oTable In objDB.Tables
    MsgBox oTable.Name    
Next

Another common use for SQL-DMO, and certainly one that gets requested in our forums on a regular basis, is scripting database objects. Scripting is quite simple. Once we have a table object, as seen in the above example, we can call the script() method and we are returned a string that contains the Create Table script for that object. There are a number of scripting options that we can set for this method, for example we can choose not to include any constraints, or only have contstraints, etc, etc, etc. But I will let you look these up for yourself in Books Online.

Here is some code that will generate a script for the employees table in the Northwind database.

'Assuming, we have set up our connection.

Dim objDB As Database
Set objDB = objDMO.Databases("northwind")

Dim oTable As Table
Set oTable = objDB.Tables("employees")

'Assuming we have a text box control named text1

Text1.Text = oTable.Script()

Now, this article would not be complete without a bit of SQL code. Knowing that we can call COM objects in our T-SQL scripts using the sp_OACreate procedure, it makes sense to put these two concepts to use. Without going into the details of how this works (click the above link for some related articles), a basic script to call DMO from T-SQL is :

declare @objDMO int
declare @objDatabase int
declare @resultCode int
declare @dbname varchar(200)
declare @tablename varchar(200)
declare @cmd varchar(300)
declare @temp varchar(8000)

Set @dbname = 'PUBS'
Set @tablename = 'Authors'


EXEC @resultcode = sp_OACreate 'SQLDMO.SQLServer', @objDMO OUT
if @resultcode = 0
print 'Created Object'

Exec @resultcode = sp_OASetProperty @objDMO, 'loginsecure', 'true'


EXEC @resultcode = sp_OAMethod @objDMO, 'Connect', NULL, '(local)'
if @resultcode = 0
print 'connected'

Set @cmd = 'databases("' + @dbname + '").tables("' + @tablename + '").script'
Exec @resultcode = sp_OAMethod @objDMO, @cmd , @temp OUTPUT, 4
print @temp


EXEC @resultcode = sp_OADestroy @objDMO
if @resultcode = 0
Print 'destroyed object'

Conclusion

This has been a very brief introduction to SQL-DMO. It is a topic that a huge book could be written on so I have barely scratched the surface. The examples here are very basic and not very useful in themselves but give you an idea as to what can be achieved. I urge you to experiment with DMO and see what you can do with it. If you have any questions, post them in the forums here and I am sure you will get some help.

For more information on SQL-DMO, look in Books Online, and do a search on MSDN. There is heaps of good stuff there.

Good luck with it!