A collection of learnings and opinions.

Monday, June 25, 2007

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?



2 comments:

Tomas said...

OK, this is just a slight improvement. Turns out I want to view the user tables AND the views. this is done by typing in:

SELECT name, type, id
FROM sysobjects
WHERE type='U' or type='V'
ORDER BY type

Tomas said...

Apparently selecting directly from sysobjects is bad practice, as this object isn't guaranteed to work the same way in future versions. A better way of doing this would be the following:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
ORDER BY FULLNAME

This gives you the name and schema of the tables in your current database.

I had to support schemas, and ended up using the following query as a databound item for my dropdownlists (bound to FullName):

SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS FullName
FROM INFORMATION_SCHEMA.TABLES
ORDER BY FullName