SQL Distributed Management Objects (SQL-DMO)

A SQL-DMO object exposes the attributes of a Microsoft® SQL Server™ 2000 component.

Distributed Management Objects (DMO) is a set of programmable objects that come with SQL Server that make it easy to programatically administer your databases. SQL-DMO is actually the foundation of Enterprise Manager, so you can pretty much do anything programatically that you can do in the management tools. Some of these tasks include :

Scripting Objects

Backing up databases

Creating jobs

Altering tables

Code snippets are from VB.NET

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

'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()


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.