Over the past few years I've written a few articles on using DMO and I've used it for a lot of small projects with good success. If you come from a development background working with objects and collections is often more intuitive than trying to figure out all the system tables/views you need to query to accomplish a given task. Now that SQL 2005 is here the management objects have been upgraded to reflect the new server as well as using the .Net framework. As I write that it occurs to me that not everyone reading this will be well grounded in objects - look for a future article that covers that in more detail. For now, I'm hoping you can paste this code into your IDE and start to see how it works.

First thing is to make sure you have SMO installed. An easy way to check is to select Project, References in the IDE and look for 'Microsoft.SQLServer.SMO'. If you don't see it you'll need to run the SQL install to get it added. You'll also need a copy of Visual Studio 2005 (any of the many versions) loaded.

Today we're trying to our feet wet with SMO. I've commented the code some to help you walk through it when you get there, but I want to talk through it first. The first real line of code is the one that contains 'Oserver = New Smo.SQLServer ("localhost"). That's where we establish a trusted (NT) connection to the server and is logically just about the top of the heap - the server object will contain databases that in turn contain tables, etc. Very similar to DMO thus far.

In DMO you could pass a variety of flags to the script method that would customize the output, I pulled this sample from one of the older articles. You can see that we're passing in a total of four flags (named constants) by adding them together to get one value. Inside DMO it will decode them back to their separate values.

SMO changes the game slightly. Instead of adding up the values of the different flags and passing them in, we're going to create an Options object and set properties as needed. I don't know that it's better than before, just different. To give you a feel for how it works I only set one property, ScriptDrops, to true. When you look at the output you'll see it has created a drop database statement. You can experiment with this by setting it to false and seeing the drop omitted, or change it to some other property and seeing how it affects the results. Once we've configured our Options object we pass it as a parameter to the Database script method.

Another difference is that DMO returned or output the script as a string. SMO does so indirectly, returning all the statements needed for that particular script into a collection (think super array). To get all the statements we will have to loop through the collection and in this case just output them to the debug window, though of course you could write them to a file or just execute them.

Further down you'll see code that begins with 'For Each oTable in'. This is demonstrating how to iterate through all the tables in the master database, identify those that start with SPT_F, and then script the create statements for them. Notice that this time I didn't pass in the Options object. It's an optional option! Below is the source code, I'm also including it here so you can  download and work with it easily. Further down is the output copied from the debug window when I ran it on my machine. Nothing in the code below will change the state of your machine, but please do experiment on a local instance that can be easily reloaded if you break something experimenting.

Imports Microsoft.SqlServer.Management
Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '6/13/06 Andy Warren/SQLServerCentral.com
        'Sample code to demonstrate scripting in SMO
        Dim oServer As Smo.Server
        Dim Scripts As System.Collections.Specialized.StringCollection
        Dim Options As Smo.ScriptingOptions
        Dim oTable As Smo.Table
        Try
            'connect to local default instance, assumes NT authentication
            oServer = New Smo.Server("localhost")
            'options are flags that tell SMO how to script an object, similar to the flags you'd pass in DMO
            Options = New Smo.ScriptingOptions
            'this one will create the "drop database" statement because its set to true
            Options.ScriptDrops = True
            'now we'll point it a database we know we have, in this case the master db. It will return
            'a collection of TSQL statements we would execute to recreate the database (the mdf/ldf, not all the
            'contents. From here we could write to a file, execute, view, whatever.
            Scripts = oServer.Databases("master").Script(Options)
            'this will dump to the debug window
            For Each S As String In Scripts
                Debug.Print(S.ToString)
            Next
            'now lets look at scripting a bunch of objects, in this case all the tables that start with 'SPT_F'
            'just to show how to do a conditional. As we find each table we send the results to the Scripts
            'string collection, then just as before loop through it to write out the individual TSQL statements
            For Each oTable In oServer.Databases("master").Tables
                If oTable.Name.ToUpper Like "SPT_F*" Then
                    Scripts = oTable.Script
                    For Each S As String In Scripts
                        Debug.Print(S.ToString)
                    Next
                End If
            Next
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        Finally
            Options = Nothing
            oServer = Nothing
        End Try
    End Sub
End Class

CREATE DATABASE [master] ON  PRIMARY 
( NAME = N'master', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'mastlog', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf' , SIZE = 512KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
EXEC dbo.sp_dbcmptlevel @dbname=N'master', @new_cmptlevel=90
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [master].[dbo].[sp_fulltext_database] @action = 'disable'
end
ALTER DATABASE [master] SET ANSI_NULL_DEFAULT OFF 
ALTER DATABASE [master] SET ANSI_NULLS OFF 
ALTER DATABASE [master] SET ANSI_PADDING OFF 
ALTER DATABASE [master] SET ANSI_WARNINGS OFF 
ALTER DATABASE [master] SET ARITHABORT OFF 
ALTER DATABASE [master] SET AUTO_CLOSE OFF 
ALTER DATABASE [master] SET AUTO_CREATE_STATISTICS ON 
ALTER DATABASE [master] SET AUTO_SHRINK OFF 
ALTER DATABASE [master] SET AUTO_UPDATE_STATISTICS ON 
ALTER DATABASE [master] SET CURSOR_CLOSE_ON_COMMIT OFF 
ALTER DATABASE [master] SET CURSOR_DEFAULT  GLOBAL 
ALTER DATABASE [master] SET CONCAT_NULL_YIELDS_NULL OFF 
ALTER DATABASE [master] SET NUMERIC_ROUNDABORT OFF 
ALTER DATABASE [master] SET QUOTED_IDENTIFIER OFF 
ALTER DATABASE [master] SET RECURSIVE_TRIGGERS OFF 
ALTER DATABASE [master] SET  DISABLE_BROKER 
ALTER DATABASE [master] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
ALTER DATABASE [master] SET DATE_CORRELATION_OPTIMIZATION OFF 
ALTER DATABASE [master] SET TRUSTWORTHY OFF 
ALTER DATABASE [master] SET ALLOW_SNAPSHOT_ISOLATION ON 
ALTER DATABASE [master] SET PARAMETERIZATION SIMPLE 
ALTER DATABASE [master] SET  READ_WRITE 
ALTER DATABASE [master] SET RECOVERY SIMPLE 
ALTER DATABASE [master] SET  MULTI_USER 
ALTER DATABASE [master] SET PAGE_VERIFY CHECKSUM  
ALTER DATABASE [master] SET DB_CHAINING ON 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[spt_fallback_db](
[xserver_name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[xdttm_ins] [datetime] NOT NULL,
[xdttm_last_ins_upd] [datetime] NOT NULL,
[xfallback_dbid] [smallint] NULL,
[name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[dbid] [smallint] NOT NULL,
[status] [smallint] NOT NULL,
[version] [smallint] NOT NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[spt_fallback_dev](
[xserver_name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[xdttm_ins] [datetime] NOT NULL,
[xdttm_last_ins_upd] [datetime] NOT NULL,
[xfallback_low] [int] NULL,
[xfallback_drive] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[low] [int] NOT NULL,
[high] [int] NOT NULL,
[status] [smallint] NOT NULL,
[name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[phyname] [varchar](127) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[spt_fallback_usg](
[xserver_name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[xdttm_ins] [datetime] NOT NULL,
[xdttm_last_ins_upd] [datetime] NOT NULL,
[xfallback_vstart] [int] NULL,
[dbid] [smallint] NOT NULL,
[segmap] [int] NOT NULL,
[lstart] [int] NOT NULL,
[sizepg] [int] NOT NULL,
[vstart] [int] NOT NULL
) ON [PRIMARY]
DROP DATABASE [master]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[spt_fallback_db](
[xserver_name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[xdttm_ins] [datetime] NOT NULL,
[xdttm_last_ins_upd] [datetime] NOT NULL,
[xfallback_dbid] [smallint] NULL,
[name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[dbid] [smallint] NOT NULL,
[status] [smallint] NOT NULL,
[version] [smallint] NOT NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[spt_fallback_dev](
[xserver_name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[xdttm_ins] [datetime] NOT NULL,
[xdttm_last_ins_upd] [datetime] NOT NULL,
[xfallback_low] [int] NULL,
[xfallback_drive] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[low] [int] NOT NULL,
[high] [int] NOT NULL,
[status] [smallint] NOT NULL,
[name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[phyname] [varchar](127) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[spt_fallback_usg](
[xserver_name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[xdttm_ins] [datetime] NOT NULL,
[xdttm_last_ins_upd] [datetime] NOT NULL,
[xfallback_vstart] [int] NULL,
[dbid] [smallint] NOT NULL,
[segmap] [int] NOT NULL,
[lstart] [int] NOT NULL,
[sizepg] [int] NOT NULL,
[vstart] [int] NOT NULL
) ON [PRIMARY]