Introduction



This article intent is to provide alternate methods to work with SQL-Server operations using (SMO) SQL-Server Management Objects which are designed for programmatic management of Microsoft SQL Server using .NET languages and PowerShell while the focus programming language is VB.NET.

Many of the operations which can be done with T-SQL can also be done in SMO.  An advantage for SMO over T-SQL is an application may need to interface with custom libraries or hardware that does not operate in SSMS (SQL-Server Management Studio) or that a developer (or team of developers) may feel comfortable working with classes that SQL. No matter the following and which is to come in part 2 offer basic to advance code samples to create data objects, interrogate objects, servers, tables, columns, constraints and more.




Important note
Before continuing, the code samples presented are meant for SQL-Server and not SQL-Express edition.

Installation

SMO Installation for your project.

To install either open “Manage NuGet packages” in your Visual Studio solution, select “Browse” followed by entering SMO, select the following packages

Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoExtended

Or use the following in the package management console.

Install-Package Microsoft.SqlServer.SqlManagementObjects -Version 140.17283.0

SMO in the code samples notes

The SMO DLL files in this solution are for SQL-Server 2012, if using a earlier or newer version than the current DLL files need to be removed in place of the proper versions of the DLL files for your version of SQL-Server. 

SMO Classes

Server class

The server class represents an instance of a SQL-Server. Create a new instance of this class with an empty constructor will default to the current SQL-Server installed. To target a specific server create an instance of the server class with a known server e.g. New Server(“Production-Web01”)

Create for default server
Dim srv = New Server

Create for a specific server
New Server("Production-Web01")

Once the server object has been created you can begin using it e.g. get the install path for SQL-Server, in this case for a specific server instance.
Public Function SqlServerInstallPath(pServerName As String) As String
    Dim srv = New Server(pServerName)
    Return srv.RootDirectory
End Function

If you want to get a list of available servers then the following code sample emulates available servers. By available meaning there may be more than you have permissions to see.

This is part of the included code samples.
Public Async Function GetServersAsync() As Task(Of List(Of String))
    Dim serverNames As New List(Of String)
 
    Await Task.Run(
        Sub()
            serverNames = SmoApplication.
                        EnumAvailableSqlServers(True).
                        AsEnumerable().
                        Select(Function(row) row.Field(Of String)("Name")).
                        ToList()
        End Sub)
 
    Return serverNames
 
End Function

Keeping with the server class, the following provides access to various properties including all it's databases.
Imports Microsoft.SqlServer.Management.Smo
Imports SMO_UtilityLibrary.Interfaces
 
Namespace Classes
    Public Class ServerDetails
        Implements IDetails
        Public Property Name As String Implements IDetails.Name
        Public Property Server() As Server
        Public Property Databases() As DatabaseCollection
        Public Property Exists As Boolean Implements IDetails.Exists
        Public Property Exception() As Exception
    End Class
End Namespace

Note the following method can be called with an optional parameter to include or exclude database objects.
Public Function GetServer(pServerName As String, Optional pLoadDatabases As Boolean = True) As ServerDetails
 
    Dim serverDetails As New ServerDetails
    Dim srv = New Server(pServerName)
 
    Try
        serverDetails.Name = pServerName
        serverDetails.Server = srv
        serverDetails.Exists = True
 
        If pLoadDatabases Then
            serverDetails.Databases = srv.Databases
        End If
 
    Catch ex As Exception
 
        If ex.Message.Contains("Failed to connect to server") Then
            serverDetails.Exists = False
            serverDetails.Exception = ex
        End If
 
    End Try
 
    Return serverDetails
 
End Function

To just return all databases for a specific server.
Public Function GetDatabases(pServerName As String) As List(Of Database)
 
    Dim srvDetails = GetServer(pServerName, True)
    Dim excludeDatabaseNames As String() = {"master", "model", "msdb"}
 
    Dim result As New List(Of Database)
 
 
    If srvDetails.Exists Then
 
        If srvDetails.Databases IsNot Nothing Then
            For Each db As Database In srvDetails.Databases
                If Not excludeDatabaseNames.Contains(db.Name) Then
                    result.Add(db)
                End If
            Next
        End If
 
    End If
 
    Return result
 
End Function

Note that when asking for all databases this will include system databases, the following excludes system databases.
Public Function GetAllDatabasesAndTables(pServerName As String, Optional pLoadDatabases As Boolean = True) As _
    List(Of DatabaseAndTables)
 
    Dim srvDetails = GetServer(pServerName, pLoadDatabases)
    Dim excludeDatabaseNames As String() = {"master", "model", "msdb"}
 
    Dim result = New List(Of DatabaseAndTables)
 
    If srvDetails.Exists Then
        If srvDetails.Databases IsNot Nothing Then
 
            For Each db As Database In srvDetails.Databases
 
                If Not excludeDatabaseNames.Contains(db.Name) Then
 
                    Dim item As New DatabaseAndTables With {.DatabaseName = db.Name, .TableNameList = New List(Of String)}
                    For Each tbl As Table In db.Tables
                        item.TableNameList.Add(tbl.Name)
                    Next
 
                    result.Add(item)
 
                End If
 
            Next
 
        End If
    End If
 
    Return result
 
End Function

Server.DatabaseCollection
Using the DatabaseCollection this permits access to database details. For instance, to obtain table names for a specific database and return an instance of a custom class TableDetails.

Imports SMO_UtilityLibrary
Imports SMO_UtilityLibrary.Interfaces
 
Namespace Classes
    Public Class TableDetails
        Implements IDetails
 
        Public Property ServerName() As String
        Public Property DatabaseName() As String
        ''' <summary>
        ''' Indicates if the object is valid
        ''' </summary>
        ''' <returns></returns>
        Public Property Exists As Boolean Implements IDetails.Exists
        ''' <summary>
        ''' Table name
        ''' </summary>
        ''' <returns></returns>
        Public Property Name As String Implements IDetails.Name
        ''' <summary>
        ''' Table names
        ''' </summary>
        ''' <returns></returns>
        Public Property NameList() As List(Of String)
        ''' <summary>
        ''' Check to see if there are tables in the database
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property HasTables() As Boolean
            Get
                Return NameList.Count > 0
            End Get
        End Property
    End Class
End Namespace

Method to obtain tables

Public Function TableNames(pDatabaseName As String) As TableDetails
 
    Dim result As New TableDetails With {.Exists = False, .DatabaseName = pDatabaseName}
    Dim srv = New Server()
    Dim database = srv.Databases(pDatabaseName)
 
    If database IsNot Nothing Then
 
        result.ServerName = srv.Name
        result.Exists = True
 
        result.NameList = database.Tables.OfType(Of Table)().
            Where(Function(tbl) (Not tbl.IsSystemObject)).
            Select(Function(tbl) tbl.Name).ToList()
 
    End If
 
    Return result
 
End Function

Database class
The database class provides access to tables within a specific database. Suppose your application needs to determine if a specific table exists in a database. The following method provides this along with other information using the TableDetails class above.

Public Function TableExists(pServer As String, pDatabaseName As String, pTableName As String) As TableDetails
    Dim result As New TableDetails With {.Exists = False}
 
    Dim srv = New Server(pServer)
 
    Dim database = srv.Databases.OfType(Of Database)().FirstOrDefault(Function(tbl) tbl.Name = pDatabaseName)
 
    If database IsNot Nothing Then
 
        result.Exists = (database.Tables.OfType(Of Table)().
                                Where(Function(tbl) (Not tbl.IsSystemObject)).
                                FirstOrDefault(Function(tbl) tbl.Name = pTableName) IsNot Nothing)
    End If
 
    Return result
 
End Function

Find a table were the full name of the table is not known or perhaps a developer is looking for a similar table from another database.

Public Function GetTableByContainingToken(pServer As Server, pDatabaseName As String, pPartialTableName As String) As Table
    Dim tblResult As Table = Nothing
 
    Dim database = pServer.Databases.OfType(Of Database)().
            FirstOrDefault(Function(tbl) tbl.Name = pDatabaseName)
 
    If database IsNot Nothing Then
 
        tblResult = database.Tables.OfType(Of Table)().
            Where(Function(tbl) (Not tbl.IsSystemObject)).
            Select(Function(tbl) tbl).
            FirstOrDefault(Function(x) x.Name.Contains(pPartialTableName))
 
    End If
 
    Return tblResult
 
End Function


From the Database class there is access to columns in a table. The following method provides the ability to determine if a specific column exists in a specific table. Note the server object has no specified server which means it uses the default server.

Public Function ColumnExists(pDatabaseName As String, pTableName As String, pColumnName As String) As Boolean
    Dim srv = New Server
    Dim exists As Boolean = False
 
    Dim database = srv.Databases.OfType(Of Database)().FirstOrDefault(Function(db) db.Name = pDatabaseName)
 
    If database IsNot Nothing Then
        Dim table = database.Tables.OfType(Of Table)().FirstOrDefault(Function(tbl) tbl.Name = pTableName)
        If table IsNot Nothing Then
            exists = (table.Columns.OfType(Of Column)().FirstOrDefault(Function(col) col.Name = pColumnName) IsNot Nothing)
        End If
    End If
 
    Return exists
 
End Function

If a column needs to be inspected the following method provides this capability using a special class built into the code sample can as with the other classes can be used in your projects.

Imports System.ComponentModel
Imports Microsoft.SqlServer.Management.Smo
 
Namespace Classes
    Public Class ColumnDetails
        ''' <summary>
        ''' Column is a identify column
        ''' </summary>
        <Category("Items"), Description("Indicates if the field is Identity")>
        Public Property Identity() As Boolean
        <Category("General"), Description("Column Name")>
        Public Property Name() As String
        ''' <summary>
        ''' There are plenty of useful properties within DataType as an
        ''' example in the property SqlDataType or IsDate (which we know
        ''' there are multiple data types).
        ''' </summary>
        <Category("Items"), Description("Describes the data type")>
        Public Property DataType() As DataType
        <Category("Items"), Description("Describes the sql data type")>
        Public ReadOnly Property SqlDataType() As SqlDataType
            Get
                Return DataType.SqlDataType
            End Get
        End Property
        '       
        ' * I setup several properties for Dates to show that we can do this but
        ' * generally speaking we don't need to do all of them.
        '        
        <Category("Items"), Description("Indicates if this field is a Date")>
        Public ReadOnly Property IsDate() As Boolean
            Get
                Return DataType.SqlDataType = SqlDataType.Date
            End Get
        End Property
        <Category("Items"), Description("Indicates if this field is a DateTime")>
        Public ReadOnly Property IsDateTime() As Boolean
            Get
                Return DataType.SqlDataType = SqlDataType.DateTime
            End Get
        End Property
        <Category("Items"), Description("Indicates if this field is a DateTime Offset")>
        Public ReadOnly Property IsDateTimeOffset() As Boolean
            Get
                Return DataType.SqlDataType = SqlDataType.DateTimeOffset
            End Get
        End Property
        <Category("Items"), Description("Indicates if this field is Nullable")>
        Public Property Nullable() As Boolean
        <CategoryAttribute("Items"), DescriptionAttribute("Indicates if field is in a primary key")>
        Public Property InPrimaryKey() As Boolean
        ''' <summary>
        ''' get foreign keys
        ''' </summary>
        <Category("Items"), Description("ForeignKeys DataTable")>
        Public Property ForeignKeys() As DataTable
        ''' <summary>
        ''' Contains row data retrieved from EnumForeignKeys
        ''' which represent any foreign key definitions
        ''' </summary>
        <Category("Items"), Description("ForeignKeys break down")>
        Public Property ForeignKeysList() As List(Of ForeignKeysDetails)
        Public Overrides Function ToString() As String
            Return Name
        End Function
    End Class
End Namespace

Get column details using the class above for the return type using the default server.

Public Function GetColumnDetails(pDatabaseName As String, pTableName As String) As List(Of ColumnDetails)
    Dim srv = New Server
    Dim columnDetails = New List(Of ColumnDetails)()
 
    Dim database = srv.Databases.OfType(Of Database)().
            FirstOrDefault(Function(db) db.Name = pDatabaseName)
 
    If database IsNot Nothing Then
 
        Dim table = database.Tables.OfType(Of Table)().
                FirstOrDefault(Function(tbl) tbl.Name = pTableName)
 
        If table IsNot Nothing Then
 
            columnDetails = table.Columns.OfType(Of Column)().
                Select(Function(col) New ColumnDetails() With
                            {
                                .Identity = col.Identity,
                                .DataType = col.DataType,
                                .Name = col.Name,
                                .InPrimaryKey = col.InPrimaryKey,
                                .Nullable = col.Nullable
                            }
                ).ToList()
 
        End If
    End If
 
    Return columnDetails
 
End Function

An overload of the above method to specify the server.

Public Function GetColumnDetails(pServer As String, pDatabaseName As String, pTableName As String) As List(Of ColumnDetails)
    Dim srv = New Server(pServer)
    Dim columnDetails = New List(Of ColumnDetails)()
 
    Dim database = srv.Databases.OfType(Of Database)().FirstOrDefault(Function(db) db.Name = pDatabaseName)
 
    If database IsNot Nothing Then
        Dim table = database.Tables.OfType(Of Table)().FirstOrDefault(Function(tbl) tbl.Name = pTableName)
 
        If table IsNot Nothing Then
 
            columnDetails = table.Columns.OfType(Of Column)().
                Select(Function(col) New ColumnDetails() With
                            {
                                .Identity = col.Identity,
                                .DataType = col.DataType,
                                .Name = col.Name,
                                .InPrimaryKey = col.InPrimaryKey,
                                .Nullable = col.Nullable
                            }
                ).ToList()
 
        End If
    End If
 
    Return columnDetails
 
End Function

The following provides access to foreign key details using the following class for a return type.

Public Function GetColumnDetails(pServer As String, pDatabaseName As String, pTableName As String) As List(Of ColumnDetails)
    Dim srv = New Server(pServer)
    Dim columnDetails = New List(Of ColumnDetails)()
 
    Dim database = srv.Databases.OfType(Of Database)().FirstOrDefault(Function(db) db.Name = pDatabaseName)
 
    If database IsNot Nothing Then
        Dim table = database.Tables.OfType(Of Table)().FirstOrDefault(Function(tbl) tbl.Name = pTableName)
 
        If table IsNot Nothing Then
 
            columnDetails = table.Columns.OfType(Of Column)().
                Select(Function(col) New ColumnDetails() With
                            {
                                .Identity = col.Identity,
                                .DataType = col.DataType,
                                .Name = col.Name,
                                .InPrimaryKey = col.InPrimaryKey,
                                .Nullable = col.Nullable
                            }
                ).ToList()
 
        End If
    End If
 
    Return columnDetails
 
End Function

Method to return foreign keys for a table in a database for the default server.

Public Function TableKeys(pDatabaseName As String, pTableName As String) As List(Of ForeignKeysDetails)
    Dim srv = New Server()
    Dim keyList = New List(Of ForeignKeysDetails)()
    Dim database = srv.Databases.OfType(Of Database)().FirstOrDefault(Function(db) db.Name = pDatabaseName)
    If database IsNot Nothing Then
        Dim table = database.Tables.OfType(Of Table)().FirstOrDefault(Function(tbl) tbl.Name = pTableName)
        If table IsNot Nothing Then
 
            ' ReSharper disable once LoopCanBeConvertedToQuery
            For Each item As Column In table.Columns.OfType(Of Column)()
 
 
                Dim fkds As List(Of ForeignKeysDetails) = item.EnumForeignKeys().
                        AsEnumerable().
                        Select(Function(row) New ForeignKeysDetails With
                                    {
                                        .TableSchema = row.Field(Of String)("Table_Schema"),
                                        .TableName = row.Field(Of String)("Table_Name"),
                                        .SchemaName = row.Field(Of String)("Name")
                                    }
                        ).ToList()
 
                For Each ts As ForeignKeysDetails In fkds
                    keyList.Add(ts)
                Next
 
            Next
        End If
    End If
 
    Return keyList
 
End Function

Copying an existing database operation

Not all developers have a development, test and production server. The next best thing is to duplicate a database. In the following method the name of an existing database is passed in as the first parameter and the second parameter is the name of a database to copy the information from the source database (parameter 1).

Public Function CopyDatabase(pOriginalDatabase As String, pNewDatabase As String) As Boolean
 
    mHasException = False
 
    Dim srv = New Server
    Dim db As Database
 
    Try
        db = srv.Databases(pOriginalDatabase)
        Dim dbCopy As Database
        dbCopy = New Database(srv, pNewDatabase)
        dbCopy.Create()
 
        Dim trans As Transfer
        trans = New Transfer(db)
        trans.CopyAllTables = True
        trans.Options.WithDependencies = True
        trans.Options.ContinueScriptingOnError = True
        trans.DestinationDatabase = pNewDatabase
        trans.DestinationServer = srv.Name
        trans.DestinationLoginSecure = True
        trans.Options.DriAllKeys = True
        trans.CopySchema = True
 
        trans.TransferData()
 
        Return True
 
    Catch ex As Exception
        mHasException = True
        mLastException = ex
        Return False
    End Try
 
End Function

Scripting tables in a database

SSMS provides the ability to script a database with or without data. With SMO using the Scripter class a developer can script out table(s) to a stream or text file for later use.

In the following method all tables for a specific database, in this case a modified version of Microsoft NorthWind database each table is scripted to one text file per table.

Public Function ScriptDatabaseTables() As List(Of String)
    Dim fileNames As New List(Of String) From {"Tables for NorthWindAzure", ""}
    ' uses default server
    Dim srv = New Server()
 
    Dim scrp As New Scripter With {
        .Server = srv
    }
 
    scrp.Options.ScriptData = True
    scrp.Options.ScriptSchema = False
    scrp.Options.ToFileOnly = True
 
    Dim database = srv.Databases("NorthWindAzure")
 
    Dim tables = database.Tables.OfType(Of Table)().
            Where(Function(tbl) (Not tbl.IsSystemObject))
 
    For Each table As Table In tables
        scrp.Options.FileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, table.Name & ".txt")
        fileNames.Add(Path.GetFileName(scrp.Options.FileName))
        scrp.EnumScript(New SqlSmoObject() {table})
    Next
 
    Return fileNames
 
End Function

Events

There are operations where a developer needs to know when an operation has began and completed. The following demonstrates dropping and creation of a table with events where in the included code sample is called from a windows form which has events setup to monitor the drop and create of the table.

''' <summary>
''' This code sample shows how to create and drop a table with events.
''' If the database exists it is dropped, no prompting.
'''
''' On table create an event is raised indicating a table was created
''' while a message is shown when the table is dropped
''' </summary>
''' <remarks>
''' * Alternate is to use TSQL script to do the same work, here the advantage
'''   for some is being able to inspect/alter properties when creating or after
'''   creating.
''' * What can go wrong: There is a live connection on the database which would
'''   cause the drop method to fail, in this case raise an exception which here
'''   is remembered and sent back to the calling method within the form.
''' </remarks>
Public Function CreateAndDropTableWithEvents(pDatabaseName As String) As Boolean
 
    mHasException = False
 
    Dim dropResults = WhenDatabaseExistsDrop(pDatabaseName)
    Dim srv = New Server()
 
    If dropResults = DatabaseExistsResult.Dropped OrElse dropResults = DatabaseExistsResult.DropNotRequired Then
 
        Dim db As New Database(srv, pDatabaseName)
        'Define a Schema object variable by supplying the parent database and name arguments in the constructor.
        'this is used in DemoTable below.
        Dim schema As Schema
        schema = New Schema(db, "kp")
        schema.Owner = "dbo"
 
        db.Create()
 
        'Create the schema on the instance of SQL Server.
        schema.Create()
 
    Else
        Return False
    End If
 
    Dim database = srv.Databases(pDatabaseName)
 
    Dim databaseCreateEventSet As New DatabaseEventSet
    databaseCreateEventSet.CreateTable = True
    databaseCreateEventSet.DropTable = True
 
    Dim serverCreateEventHandler As ServerEventHandler
    serverCreateEventHandler = New ServerEventHandler(AddressOf CreateDropTableEventHandler)
 
    'Subscribe to the first server event handler when a CreateTable event occurs.
    database.Events.SubscribeToEvents(databaseCreateEventSet, serverCreateEventHandler)
    database.Events.StartEvents()
 
    'Create a table on the database.
    'Create three most populate field types, primary key; integer, string field, date field
    Dim tb As Table
    tb = New Table(database, "DemoTable")
 
    Dim primaryIdentifierColumn As New Column(tb, "ID", DataType.Int)
    primaryIdentifierColumn.Identity = True
    primaryIdentifierColumn.IdentitySeed = 1
    primaryIdentifierColumn.Nullable = False
    tb.Columns.Add(primaryIdentifierColumn)
 
    Dim nameColumn As Column
    nameColumn = New Column(tb, "Name", DataType.NChar(50))
    nameColumn.Collation = "Latin1_General_CI_AS"
    nameColumn.Nullable = True
    tb.Columns.Add(nameColumn)
 
    Dim joinDateColumn As New Column(tb, "JoinedDate", DataType.DateTime)
    joinDateColumn.AddDefaultConstraint() ' you can specify constraint name here as well
    joinDateColumn.DefaultConstraint.Text = "GETDATE()"
    tb.Columns.Add(joinDateColumn)
 
    ' Add primary key index to the table
    Dim primaryKeyIndex As New Index(tb, "PK_TestTableIdentifier")
    primaryKeyIndex.IndexKeyType = IndexKeyType.DriPrimaryKey
    primaryKeyIndex.IndexedColumns.Add(New IndexedColumn(primaryKeyIndex, "ID"))
    tb.Indexes.Add(primaryKeyIndex)
 
    tb.Schema = "kp"
    tb.Create()
 
    '
    ' Read script to insert serveral record from disk followed by performing the inserts/
    '
    Try
        database.ExecuteNonQuery(File.ReadAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Scripts", "DemoTableRecord.txt")))
    Catch ex As Exception
        mHasException = True
        mLastException = ex
        database.Events.StopEvents()
    End Try
 
    Try
        'Remove the table.
        tb.Drop()
        ' drop database
        database.Drop()
 
        'Wait until the events have occured.
        Dim dummy As Integer
        For outer = 1 To 1000000000
            dummy = outer * 2
        Next
 
    Catch ex As Exception
        mHasException = True
        mLastException = ex
    Finally
        'Stop event handling listening
        database.Events.StopEvents()
    End Try
 
    Return True
 
End Function

Visual Studio solution

All SMO operations are housed in a class project while the implementation is in a Windows form. By separating SMO from the User Interface a developer can use the SMO functions in other types of projects.



Database for code sample

The database for this article must be created before running the example. To create the database, open CreateDatabaseTablePopulate.sql under the forms project beneath the folder Databasescript. Connect to the database via the connect button followed by executing the script.

Summary

In this article method have been presented to work with databases, tables, columns and keys along with scripting basics, copying databases and working with events. In the next part of this series working with backing up and restoring databases will be explored along with altering databases, tables, keys, indexes and more.

See also

How to: Create a shared library
Easy SQL Server Tool

Source code

https://github.com/karenpayneoregon/LearningSMObasicsWithVisualBasic