Retrieving SQL Server 2005 Database Info using SMO: Scripting Tables, Views, Stored Procedures - How to script an SQL Server table using SMO
(Page 4 of 6 )
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
While en.MoveNext
Me.TextBox1.Text &= en.Current & ControlChars.NewLine
End While
End Sub
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
While en.MoveNext
Me.TextBox1.Text &= en.Current & ControlChars.NewLine
End While
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!
Next: Adding a few more routines to util.vb >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 17 
