Retrieving SQL Server 2005 Database Info using SMO: Scripting Tables, Views, Stored Procedures - How to retrieve all column info from an SQL Server instance using SMO
(Page 3 of 6 )
The following code forms the heart of column information retrieval.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
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 Column
Dim dtReport As DataTable = util.getStructColumnBasic
For Each col In tbl.Columns
Dim drReport As DataRow = dtReport.NewRow
drReport ("ColumnName") = col.Name
drReport ("DataType") = col.DataType.ToString
drReport ("IsIdentity") = col.Identity.ToString
drReport ("IsPrimaryKey") = col.InPrimaryKey.ToString
drReport ("IsForeignKey") = col.IsForeignKey.ToString
drReport ("CanContainNulls") = col.Nullable.ToString
drReport ("Indexes") = col.EnumIndexes.Rows.Count
drReport ("Relations") = col.EnumForeignKeys.Rows.Count
dtReport.Rows.Add(drReport)
Next
Me.DataGridView1.DataSource = dtReport
End Sub
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”).
Next: How to script an SQL Server table using SMO >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 17 
