Retrieving SQL Server 2005 Database Info using SMO: Scripting Tables, Views, Stored Procedures
This article is the third in a series focusing on retrieving SQL Server 2005 information using SMO along with Visual Basic 2005 and Visual Studio 2005. In this article, we mainly deal with four topics: retrieving the column information of a selected table; scripting the selected “table;” scripting the selected “stored procedure;” and scripting the selected “view.”
A downloadable file for this article is available here.
Since I already explained SMO, DMO and more in the first article of this series, I shall dive directly into the technical issues of using SMO. I also covered the concept of “working with SMO using Visual Studio 2005” in my first article. If you are new to SMO (or having any trouble working with a new Visual Studio 2005 solution), I strongly suggest you go through that article.
Creating our own flexible routines to make them reusable
The following function “getStructColumnBasic” is one of the routines which is defined in “util.vb” (from the downloadable zip) to mainly hold database column information.
Public Function getStructColumnBasic() As DataTable
Dim dt As New DataTable
With dt.Columns
.Add(New DataColumn("ColumnName"))
.Add(New DataColumn("DataType"))
.Add(New DataColumn("IsIdentity"))
.Add(New DataColumn("IsPrimaryKey"))
.Add(New DataColumn("IsForeignKey"))
.Add(New DataColumn("CanContainNulls"))
.Add(New DataColumn("Indexes"))
.Add(New DataColumn("Relations"))
End With
Return dt
End Function
It mainly creates a data table with a column structure to hold “database column” information from a particular table in a database existing in an SQL Server instance. The column “ColumnName” holds the column name. The column “DataType” holds the data type of the column, and the naming scheme works similarly for the rest.
The following is another routine which returns a “list of databases” (defined in “util.vb”):
Public Function getDatabaseList(ByRef svr As Microsoft.SqlServer.Management.Smo.Server) As DataTable
Dim dt As New DataTable
With dt.Columns
.Add("DatabaseName")
End With
For Each db As Microsoft.SqlServer.Management.Smo.Database In svr.Databases
Dim dr As DataRow = dt.NewRow
dr("DatabaseName") = db.Name
dt.Rows.Add(dr)
Next
Return dt
End Function
The following is another routine which returns a “list of tables” (defined in “util.vb”):
Public Function getTableList(ByRef svr As Microsoft.SqlServer.Management.Smo.Server, ByRef db As Microsoft.SqlServer.Management.Smo.Database) As DataTable
Dim dt As New DataTable
With dt.Columns
.Add("TableName")
.Add("Schema")
End With
For Each tbl As Microsoft.SqlServer.Management.Smo.Table In db.Tables
To prepare this utility, we need to load the database list into a dropdown list. Once you select some databases from the database dropdown list, you need to populate another dropdown list with the entire tables list (related to the selected database). This section deals with the same.
Let us consider the following code first:
Private Sub Form7_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
The above code populates a dropdown list with all the database names. When the user selects any database from the list, the following code populates all the table names:
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
The most important statements from the above code fragment are the following:
Imports Microsoft.SqlServer.Management.Smo
The above statement imports the necessary SMO namespace (which maintains the central SMO hierarchy).
Dim svr As Server = New Server(".\sql2k5")
Dim db As Database = svr.Databases(Me.ComboBox1.Text)
The above statements connect to the local named instance (“sql2k5”) and links with the database selected from the first dropdown list.
Dim tbl As Table = db.Tables(Me.ComboBox2.Text, Me.ComboBox2.SelectedValue)
Dim col As Column
Dim dtReport As DataTable = util.getStructColumnBasic
For Each col In tbl.Columns
The first statement connects to the selected table from the second dropdown list. I am using the routine defined in “util.vb” to prepare the “Column Information” structure. “columns” is a collection which holds all the information about the columns existing in a particular table. This needs to be invoked using a table object (in this case, it is “tbl”).
This is one of the most interesting aspects of SMO. If you would like to generate a script of a particular table, in general, you need to generate it using a tool (or manually). This section shows you how to do it dynamically. That means you will be able to generate a script of a particular object (upon selecting it) using SMO very easily.
Let us start with the code first.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.TextBox1.Text = ""
Dim svr As Server = New Server(".\sql2k5")
Dim db As Database = svr.Databases(Me.ComboBox1.Text)
Dim tbl As Table = db.Tables(Me.ComboBox2.Text, Me.ComboBox2.SelectedValue)
Dim col As Specialized.StringCollection = tbl.Script()
Dim en As Specialized.StringEnumerator = col.GetEnumerator
In the above code, I tried to reuse the same type of layout (but datagridview replaced with a multi-line text box). The heart of the code lies in the following statements:
Dim col As Specialized.StringCollection = tbl.Script()
Dim en As Specialized.StringEnumerator = col.GetEnumerator
The first statement generates the script of the “table” object and returns the entire script in the form of a string collection. As it is a collection, I use an enumerator to go though each and every line of the collection and finally append the string (or line of script) to the textbox. It is as simple as that!
To help myself with better modularization, I defined few more routines within “util.vb” as follows:
Public Function getStoredProcedureList(ByRef svr As Microsoft.SqlServer.Management.Smo.Server, ByRef db As Microsoft.SqlServer.Management.Smo.Database) As DataTable
Dim dt As New DataTable
With dt.Columns
.Add("SPName")
.Add("Schema")
End With
For Each sp As Microsoft.SqlServer.Management.Smo.StoredProcedure In db.StoredProcedures
Dim dr As DataRow = dt.NewRow
dr("SPName") = sp.Name
dr("Schema") = sp.Schema
dt.Rows.Add(dr)
Next
Return dt
End Function
The above routine mainly returns all the stored procedures along with their schemas available in the specified database and instance (database and instance are part of arguments).
Public Function getViewsList(ByRef svr As Microsoft.SqlServer.Management.Smo.Server, ByRef db As Microsoft.SqlServer.Management.Smo.Database) As DataTable
Dim dt As New DataTable
With dt.Columns
.Add("ViewName")
.Add("Schema")
End With
For Each vw As Microsoft.SqlServer.Management.Smo.View In db.Views
Dim dr As DataRow = dt.NewRow
dr("ViewName") = vw.Name
dr("Schema") = vw.Schema
dt.Rows.Add(dr)
Next
Return dt
End Function
The above routine mainly returns all the views along with their schemas available in the specified database and instance (database and instance are part of arguments).
Once you add the above routines, don’t forget to populate “ComboBox2” accordingly (either by using “getStoredProceduresList” or “getViewsList”).
The code to generate scripts will be pretty similar to that of previous sections (but carefully observe the different classes I used). Let us proceed to the code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.TextBox1.Text = ""
Dim svr As Server = New Server(".\sql2k5")
Dim db As Database = svr.Databases(Me.ComboBox1.Text)
Dim sp As StoredProcedure = db.StoredProcedures(Me.ComboBox2.Text, Me.ComboBox2.SelectedValue)
Dim col As Specialized.StringCollection = sp.Script()
Dim en As Specialized.StringEnumerator = col.GetEnumerator
You can not only generate scripts of Tables, Views and Stored Procedures, but also for several other different types of database objects present in a SQL Server database. And in fact, you can script an entire database as well! I leave it to you to further enhance the solution.
The entire demonstration solution has been developed using SQL Server 2005 Enterprise Edition and Visual Studio 2005 Professional Edition on Windows Server 2003 Standard Edition. Please note that I didn’t really test the solution on any other versions/editions of similar suites of Microsoft products.
Any comments, suggestions, ideas, improvements, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.
MS SQL Server 
/ 24 
Retrieving SQL Server 2005 database information using SMO: properties of databas
