Which tables do I have available?

Sometimes when you're connecting to a database you'd like to know what tables are available. I'm no DBA, so it took me a while to find out how to do that.



In this case I've just gotten the connection stuff from the user, and I run this code when the user presses a "Connect" button to connect to the database the user has specified and populate a drop-down box with the user-tables in that database. The real meat here is the selectTables string in there, which gets the user-tables from the sysobjects table. Once I get this I bind the drop-down box (ddTableSelect) to these to show all the tables.

Protected Sub btnConnect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConnect.Click

	Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder



	builder.DataSource = txtDatasource.Text

	builder.InitialCatalog = txtInitialCatalog.Text

	builder.UserID = txtUserID.Text

	builder.Password = txtPassword.Text



	Dim selectTables As String = "SELECT sysobjects.name, sysobjects.id " _

	  & "FROM sysobjects " _

	  & "WHERE sysobjects.type = 'U'"



	Dim source As SqlDataSource = New SqlDataSource(builder.ConnectionString, selectTables)



	ddTableSelect.DataSource = source

	ddTableSelect.DataTextField = "name"

	Try

		ddTableSelect.DataBind()

		connectedState()

	Catch ex As Exception

		lblError.Text = "Could not connect: " & ex.Message

		errorState()

	End Try

End Sub

To be perfectly clear, as VB can be a real pain on the eyes (and SQL is soooo much better, right!?) this is the SQL that gets the tables:

SELECT sysobjects.name, sysobjects.id 

    FROM sysobjects

    WHERE sysobjects.type = 'U'

Yes, I know, catching all exceptions in the end there is bad, but this is me feeling my way, ok?