VB-Tips

SQL Server: List Database Tables

Use the sp_tables stored procedure to list the tables in a database. This example will list all the databases and tables for the local sql server.
Imports System.Data.SqlClient

Public Module Module1

    Sub Main()
        Dim strConn As String
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim dr, drTables As SqlDataReader

        strConn = "Server =(local);"
        strConn &= "Database = ; Integrated Security = SSPI;"

        conn = New SqlConnection(strConn)
        cmd = New SqlCommand("sp_Databases", conn)

        cmd.CommandType = CommandType.StoredProcedure

        conn.Open()
        dr = cmd.ExecuteReader

        If dr.HasRows Then
            Do While dr.Read
                Console.WriteLine(String.Format("Name {0} Size {1}", _
                        dr.Item("Database_Name"), dr.Item("Database_Size")))
                Dim connTable As SqlConnection
                strConn = "Server =(local);"
                strConn &= "Database = " & dr.Item("Database_Name").ToString
                strConn &= "; Integrated Security = SSPI;"

                connTable = New SqlConnection(strConn)

                Dim cmdTables As New SqlCommand("sp_Tables", connTable)

                connTable.Open()
                drTables = cmdTables.ExecuteReader
                Do While drTables.Read
                    Console.Write("   ")
                    Console.WriteLine(drTables.Item("TABLE_NAME").ToString)
                Loop
                drTables.Close()
                connTable.Close()
            Loop
        End If

        dr.Close()
        conn.Close()
    End Sub

End Module

---