They say you learn something new every day.

Listing Tables (15/01/2012)

Occasionally, I’ve thought it would be useful to list all the tables in a database, but I’ve never actually tried to do it. It turns out there are different ways for different database engines.

Yesterday I was trying to list all the tables in access and I came across this code:

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)

objConnection.Open _
  “Provider = Microsoft.Jet.OLEDB.4.0;Data Source = ‘C:\Scripts\Test.mdb’” 

Set objRecordSet = objConnection.OpenSchema(20)

Do Until objRecordset.EOF
   Wscript.Echo “Table name: ” & objRecordset.Fields.Item(“TABLE_NAME”)
   objRecordset.MoveNext
Loop

I’ve cleaned it up a bit, but it’s amazing – you just have to enter “20” to get a list of tables.

You do get system tables as well, so you need to filter them out, but this is great (at least for Access).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Tag Cloud

%d bloggers like this: