List stored procedures/tables using SMO

[WarrenW]

Hi Everyone,

I have just started to learn how to use SMO in some cases. Below is some code I found from a site to list the servers and databases for each. What I would like to do is go a step further and list all of the databases and stored procedures in their own listboxes when I select a database. I would like to not include the system ones or at least have a checkbox to decide to include them or not. I searched and could not find how to do this.

Here is the code I have so far:

Imports Microsoft.SqlServer
Imports Microsoft.SqlServer.Server
Imports Microsoft.SqlServer.Management
Imports Microsoft.SqlServer.Management.Smo


Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'just list local servers, set to false if you want to see all servers
        Dim dataTable = SmoApplication.EnumAvailableSqlServers(False)
        lstServers.ValueMember = "Name"
        lstServers.DataSource = dataTable

    End Sub

    Private Sub lstServers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstServers.SelectedIndexChanged
        lstDatabases.Items.Clear()
        If lstServers.SelectedIndex <> -1 Then
            Dim serverName As String = lstServers.SelectedValue.ToString()
            Dim server As Server = New Server(serverName)
            Try
                For Each database As Database In server.Databases
                    lstDatabases.Items.Add(database.Name)
                Next
            Catch ex As Exception
                Dim exception As String = ex.Message
            End Try
        End If
    End Sub

Thanks!

Warren

[dee-u]

Have a look at this, it may help you out.

[techgnome]

Once you have a database object, loop through the StoredProcedures, Views and UserDefinedFunctions collections....

    Private Sub ListObjects(ByVal ServerName As String, ByVal DatabaseName As String)
        sqlServer = New Microsoft.SqlServer.Management.Smo.Server(ServerName)
        sqlServer.ConnectionContext.ConnectionString = String.Format(CONNECTION_String, ServerName, DatabaseName)

        Dim sqlDatabase As Microsoft.SqlServer.Management.Smo.Database = sqlServer.Databases(DatabaseName)
        'Get all sprocs
        For Each dbSProc As Microsoft.SqlServer.Management.Smo.StoredProcedure In sqlDatabase.StoredProcedures

            'dbSProc is a Stored Procedure object

        Next
        'get all views
        For Each dbView As Microsoft.SqlServer.Management.Smo.View In sqlDatabase.Views

            'dbView is a View object

        Next
        'get all functions
        For Each dbFunction As Microsoft.SqlServer.Management.Smo.UserDefinedFunction In sqlDatabase.UserDefinedFunctions

            'dbFunction is a UDF Object

        Next

        sqlServer.ConnectionContext.Disconnect()

    End Sub

-tg