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...
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)
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
Click Browse button on Visual Studio Reference Manager dialog screen to point and select SMO libraries from where are installed.
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.
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.
Download SQL Server Management Objects Sample Project
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.