Retrieving SQL Server 2005 Database Info using SMO: Scripting Tables, Views, Stored Procedures - How to script stored procedures and views (or others) in SQL Server using SMO
(Page 6 of 6 )
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
While en.MoveNext
Me.TextBox1.Text &= en.Current & ControlChars.NewLine
End While
End Sub
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 vw As View = db.Views(Me.ComboBox2.Text, Me.ComboBox2.SelectedValue)
Dim col As Specialized.StringCollection = vw.Script()
Dim en As Specialized.StringEnumerator = col.GetEnumerator
While en.MoveNext
Me.TextBox1.Text &= en.Current & ControlChars.NewLine
End While
End Sub
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 17 
