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”)
“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”)
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).