Retrieving SQL Server 2005 Database Info using SMO: Scripting Tables, Views, Stored Procedures
(Page 1 of 6 )
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
Dim dr As DataRow = dt.NewRow
dr("TableName") = tbl.Name
dr("Schema") = tbl.Schema
dt.Rows.Add(dr)
Next
Return dt
End Function
Next: Populating the dropdown lists for “databases” and “tables” using SMO >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 14 
