SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server Tools Reviews and Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



SQL Server Management Objects SMO to Generate Database Table Scripts

Microsoft .NET programmers can use SQL Server Management Objects (SMO) which provides a collection of objects required for managing Microsoft SQL Server through programming. SQL Server database administrator and developers can create programs to perform SQL Server related tasks automatically using SQL Server Management Objects (SMO) like to generate database table scripts for selected database on a SQL Server instance.

In this SQL Server tutorial, I want to share with developers how they can create a VB.NET project in Visual Studio 2015 referencing SQL Server Managemet Objects (SMO) libraries. As a second step, using a VB.NET Windows GUI we will be able to connect to a SQL Server instance and list all databases on the form. The last step will be selecting a SQL database to script all tables existing in that database and saving SQL script file using a File Save dialog control.

Before SQL programmers continue reading the SQL Server Management Objects tutorial, they can see the final SQL tool which generates create scripts of all tables in a selected database in action.

I hope database developers find this SQL Server SMO tutorial useful because generating database table scripts is a frequent task for many SQL Server administrator and developers managed for various reasons.


Create Visual Studio Project using SQL Server Management Objects (SMO)

Let's start with creating our SMO (SQL Server Management Objects) project using Visual Studio 2015.

Launch Visual Studio
Create new project using menu options: File > New > Project...

create new project for SQL Server in Visual Studio

Next step is choosing the right step for our application for SMO tasks.
You can develop your project in VB.NET as I did in this tutorial or develop in Visual C#
To display response visually for the sake of this tutorial, I preferred a Windows Form application.
But for automated task through SQL Server Management Objects SMO libraries, a console application could be also preferred.

I completed development project template selection as seen in following screenshot.
In this SQL tutorial, we will be building a Windows Forms application in Visual Basic using SQL Server Management Objects (SMO)

Visual Studio project template selection for SQL Server Management Objects


SQL Server Management Objects (SMO) Assembly Files as Project Reference in Visual Studio

In our SMO project, we need to add SMO libraries (SQL Server Management Objects) as project reference.
Programmers require each of following SMO assemblies to add as reference to their SQL Server project:
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.ConnectionInfo.dll

To add SQL Server Management Objects dll files as project Reference in Visual Studio on Solution Explorer windows, right click on the References under project name. Select Add Reference... on context menu

add project Reference in Visual Studio

Click Browse button on Visual Studio Reference Manager dialog screen to point and select SMO libraries from where are installed.

Visual Studio Reference Manager

Point to: C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies for SQL Server 2014 assemblies.
If you have other SQL Server versions installed on your server, you can browse the installation path for corresponding version including SDK and Assemblies folder.

Select above mentioned 4 assembly files and press Add button. Then click OK to close Reference Manager dialog screen.


Windows Form Design for SQL Server Management Objects Project

The form design of the final product is important. For the simplicity of this tutorial, I preferred to enable the user to provide a SQL Server instance name manually to connect and list databases created on that SQL Server installation. When the user selects one of the databases, using a button database administrator or SQL programmer will be able to generate create table scripts of selected database and save them locally.

Maybe it is not created through Design Thinking principles but I believe below Windows Form application design is a good starting point for our requirements.

GUI for SQL Server Management Objects scripting task project
SQL Server Management Objects scripting task project GUI design

On Form1.vb Windows form in design mode, programmers can add UI controls from Visual Studio toolbox as seen in below screenshot.

Label with name as Label1 and text as Server
TextBox with name as txtServer set to empty string in design mode.
Button named btnConnectServer with text List Databases to connect to target SQL Server instance
ListBox named listDatabases for database list for selected SQL Server
Button named btnScriptDatabase and text Script Tables
Button named btnExit displaying text Exit for closing the app.
Finally, add a SaveFileDialog control with name SaveFileDialog1 on to the form design layout.


SQL Server Management Objects Project Codes to Script Database Tables Create Table Scripts

.NET Developers are now ready to switch to code editor on Visual Studio IDE. A double click on the form layout will let programmer to Code Editor view in Visual Studio.

If you have named controls added on the layout with exactly the same names as I give above, when the Form1.vb code editor is displayed, copy and paste below code by replacing all existing code on the form.
If the control names are different, you will have to refactor below code for correct names before building your project.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Sdk.Sfc
Imports System.Collections.Specialized
Imports System.IO
Imports System.Text

Public Class Form1

 Dim myServer As Server

 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  txtServer.Text = "localhost"
 End Sub

 Private Sub btnConnectServer_Click(sender As Object, e As EventArgs) Handles btnConnectServer.Click

  If txtServer.Text = "" Then
   Exit Sub
  Else
   myServer = New Server( txtServer.Text )
  End If
  listDatabases.Items.Clear()

  Dim srv = myServer
  For Each dbitem In srv.Databases
   listDatabases.Items.Add(New InstanceDatabase(dbitem.ToString))
  Next

 End Sub

 Public Class InstanceDatabase
  Public DatabaseName As String

  Public Sub New(ByVal _DatabaseName As String)
   DatabaseName = _DatabaseName
  End Sub

  Public Overrides Function ToString() As String
   Return DatabaseName
  End Function
 End Class

 Private Sub btnScriptDatabase_Click(sender As Object, e As EventArgs) Handles btnScriptDatabase.Click

  Dim sb = New StringBuilder()

  If myServer Is Nothing Then
   Exit Sub
  End If
  Dim srv = myServer

  Dim dbname As String = listDatabases.SelectedItem.ToString
  dbname = dbname.Replace("[", "")
  dbname = dbname.Replace("]", "")

  Dim db = srv.Databases(dbname)

  Dim scrpt = New Scripter(srv)
  scrpt.Options.ScriptDrops = False

  Dim obj = New Urn(0) {}
  For Each tbl As Table In db.Tables
   obj(0) = tbl.Urn
   If tbl.IsSystemObject = False Then
    Dim sc As StringCollection = scrpt.Script(obj)
    For Each st In sc
     sb.Append(st)
     sb.Append(vbNewLine)
    Next
   End If
  Next

  SaveFileDialog1.Filter = "SQL|*.sql"
  SaveFileDialog1.Title = "Save an SQL Script File"
  SaveFileDialog1.FileName = String.Concat("SQLTableScript_", myServer, "_", dbname)
  SaveFileDialog1.ShowDialog()

  If SaveFileDialog1.FileName <> "" Then
   If Not File.Exists(SaveFileDialog1.FileName) Then
    File.WriteAllText(SaveFileDialog1.FileName, sb.ToString())
   End If
  End If

 End Sub

 Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
  Me.Close()
 End Sub

End Class

If .NET programmers look at the code carefully, they will realize at the top of the form we have imported referenced libraries. So that developer can refer SMO objects or SQL Server Management Objects in the Visual Basic code.

Here is how SQL Server Management Objects (SMO) libraries are imported in VB.NET source codes

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Sdk.Sfc
Imports System.Collections.Specialized
Imports System.IO
Imports System.Text

Additionally we need System.IO and System.Text to download and save all table scripts as a single file on selected target path.

Using SMO, it is possible to connect to a SQL Server instance by using Server object.
New Server() by providing the instance name will create an object reference to the SQL Server database instance which is ready to connect by default with Windows authentication.

When application is loaded for first time, I automatically display "localhost" in server instance textbox. The user is free to change the SQL Server instance name. Pressing the btnConnectServer button creates a Server object for related SQL Server instance using SMO libraries.

In the same event handler, I use a For Each loop which loops through each database in the target SQL Server instance. Every database name is added to the listbox control.

The database collection, or list of all databases can be reached using Server.Databases collection.

When the user choose a database name from the all available databases list in listbox and then clicks btnScriptDatabase button, click event is triggered.

To create scripts in SQL Server Management Objects (SMO), Scripter object is used. Scripter is created by passing the SQL Server instance as an input argument.

For creating script for a specific database object, Scripter.Script method is used in SMO. By passing the database object, in our tutorial we pass each table object one by one to Scripter object Script method through a For Each loop on db.Tables collection for selected database.

The last section in our sample SQL Server SMO codes is related with SaveFileDialog control which is used to save all scripts as a text file (.sql)
Programmers can use File.WriteAllText method to save Scripter object Script method outputs which are stored as string using a StringBuilder instance.

Finally, developers are ready to Build project in Visual Studio IDE and test their SQL Server application which will connect to a SQL Server instance, list all databases for the user to select one among them and enable the user to get Create Table scripts for all existing tables in target database.

In Visual Studio, using top menu follow Build > Rebuild Solution for building your SQL Server SMO project. By default project will be build in debug mode. After you complete all development task, before deploying your solution to productive environmen, build it in release mode and then deploy it.

build SQL Server SMO project in visual Studio


Download SQL Server Management Objects Sample Project

SQL You can download SQL Server SMO sample project ready to open in Visual Studio for review and improve by using following link: SQL Server Management Objects (SMO) for database script sample project.

SQL Server programmers can use sample SQL Server Management Objects (SMO) Visual Studio project freely in their developments. I hope you find this SQL Server tutorial useful which is developed to generate database table scripts.






Related SQL Resources

SQL Server Articles

SQL Server 2016

SQL Server 2014

SQL Server 2012

SQL Server Tools

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems