How to loop through SQL Server tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kandar7272@gmail.com - 28 Dec 2006 00:51 GMT

Hi - I'm trying to work out a way to loop through (or just get a list
of) all of the tables in an SQL Server (2005) database from Access.
Based on the name of the table, I will then proceed to do some action
with it. I want to use a DSNless connection. I have searched but
haven't been able to sort it out just yet. Any ideas?

(Note: I tried with DAO but my OpenDatabase kept failing - I think it
prefers a DSN and I'm trying DSNless. I also can connect with ADO but
then can't seem to figure out how to get a list of available tables).

Reply to this Message

Lyle Fairfield - 28 Dec 2006 01:24 GMT

Kandar7272@gmail.com wrote in news:1167267102.836756.85820
@f1g2000cwa.googlegroups.com:

> Hi - I'm trying to work out a way to loop through (or just get a list
> of) all of the tables in an SQL Server (2005) database from Access.
> Based on the name of the table

<snips>

> I also can connect with ADO but
> then can't seem to figure out how to get a list of available tables).

Public Sub GetTableNames()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim f As ADODB.Field
Set c = New ADODB.Connection
With c
.Provider = "sqloledb.1"
With .Properties
.Item("Data Source") = "server"
.Item("Initial Catalog") = "database"
.Item("PassWord") = "password"
.Item("User ID") = "userid"
End With
.Open
Set r = .OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "TABLE"))
With r
' show the field names
' information available
For Each f In .Fields
Debug.Print f.Name
Next f
Debug.Print
' show the table names
While Not .EOF
Debug.Print .Fields("TABLE_NAME"), .Fields("TABLE_TYPE")
.MoveNext
Wend
End With
End With
End Sub

Signature

Lyle Fairfield

Reply to this Message

Kandar7272@gmail.com - 28 Dec 2006 16:44 GMT

Great - thx! - works perfectly. I am a little unclear on how one would
determine what arguments are available in the Array( ) portion of the
OpenSchema line. I will continue to look into it, but if you feel like
providing additional info, great! Thanks again though.

> Kandar7...@gmail.com wrote in news:1167267102.836756.85820
> @f1g2000cwa.googlegroups.com:
[quoted text clipped - 38 lines]
> --
> Lyle Fairfield

Reply to this Message

Lyle Fairfield - 28 Dec 2006 18:18 GMT

> Great - thx! - works perfectly. I am a little unclear on how one
> would determine what arguments are available in the Array( ) portion
> of the OpenSchema line. I will continue to look into it, but if you
> feel like providing additional info, great! Thanks again though.

The fields were:

1. TABLE_CATALOG
2. TABLE_SCHEMA
3. TABLE_NAME
4. TABLE_TYPE
5. TABLE_GUID
6. DESCRIPTION
7. TABLE_PROPID
8. DATE_CREATED
9. DATE_MODIFIED

The qualifier was

Array(Empty, Empty, Empty, "TABLE")

which specified

1. no criterion for TABLE_CATALOG
2. no criterion for TABLE_SCHEMA
3. no criterion for TABLE_NAME

4. TABLE_TYPE="Table" (which excluded the System Tables); We might have
specified "ALIAS", "TABLE", "SYNONYM","SYSTEM TABLE","VIEW","GLOBAL
TEMPORARY", "LOCAL TEMPORARY" or "SYSTEM VIEW"

We could have included more Emptys for
5. TABLE_GUID
6. DESCRIPTION
7. TABLE_PROPID
8. DATE_CREATED
9. DATE_MODIFIED
but that was unnecessary as ADO OpenSchema defaults to Empty after the last
Field referenced (so no Array will return all information available).

We could replace any Empty with an Equal Criterion for that Field.

If we specify
adSchemaProviderSpecific
and a
SchemaID
we may get other fields; I have not explored this.

Signature

Lyle Fairfield

Reply to this Message

Kandar7272@gmail.com - 28 Dec 2006 20:11 GMT

Thanks - very interesting and useful. chrs

> Kandar7...@gmail.com wrote innews:1167324263.612337.326820@a3g2000cwd.googlegroups.com:
>
[quoted text clipped - 46 lines]
> --
> Lyle Fairfield

Reply to this Message

Tom van Stiphout - 28 Dec 2006 02:33 GMT

I think Lyle's solution is more elegant than mine, but you could
certainly link sysobjects table to your MDB, and then select from it.
I believe tables have a Type='U'.

-Tom.

>Hi - I'm trying to work out a way to loop through (or just get a list
>of) all of the tables in an SQL Server (2005) database from Access.
[quoted text clipped - 5 lines]
>prefers a DSN and I'm trying DSNless. I also can connect with ADO but
>then can't seem to figure out how to get a list of available tables).

Reply to this Message

Kandar7272@gmail.com - 28 Dec 2006 16:45 GMT

Thanks! - this options works as well. I will explore this table
further...seems like it could be useful in many ways.

> I think Lyle's solution is more elegant than mine, but you could
> certainly link sysobjects table to your MDB, and then select from it.
[quoted text clipped - 11 lines]
> >prefers a DSN and I'm trying DSNless. I also can connect with ADO but
> >then can't seem to figure out how to get a list of available tables).- Hide quoted text -- Show quoted text -

Reply to this Message

Tom van Stiphout - 29 Dec 2006 02:03 GMT

sysobjects and the many other system tables and the several catelog
views (don't forget these; handy summary views!) are well documented
in SQL Server Books Online.

-Tom.

>Thanks! - this options works as well. I will explore this table
>further...seems like it could be useful in many ways.
[quoted text clipped - 14 lines]
>> >prefers a DSN and I'm trying DSNless. I also can connect with ADO but
>> >then can't seem to figure out how to get a list of available tables).- Hide quoted text -- Show quoted text -

Reply to this Message

Chuck Grimsby - 28 Dec 2006 23:13 GMT

Make a Pass-Thru query to your database and use:

Exec SP_Tables

As your query.

(If you don't already know this, be sure to set the ODBC connection
string in the Properties of the query.)

See the SQL BOL for more information on SP_Tables, and you might also
want to look at SP_Columns for a list of the columns in a table.

Signature

Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Reply to this Message