Retrieving SQL Server 2005 Database Info using SMO: Scripting Tables, Views, Stored Procedures - Adding a few more routines to util.vb
(Page 5 of 6 )
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”).
Next: How to script stored procedures and views (or others) in SQL Server using SMO >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 17 
