Query Remote Server for Table List

[former_SQLA_member1694868]

Having successfully connected to an MS Access database via ODBC, I am now trying to make the process of making proxy tables even faster.

How do you query a remote server (any ODBC remote server already defined at this point) for a list of tables? I want to use the list to run a list of statements against to create proxy tables?

I used the Migrate server commands successfully for the most part, but they run into trouble if you have any tables with the same name. Also, I may be getting different versions of the same file and it would be useful to increment them somehow, probably using something attached to the tablename such as S1TableName, S2TableName etc. or TableNameS1, TableNameS2 etc. for different sources of the same table.

I tried looking at the transaction log to see what SQL is run when Sybase Central pulls the list, but it looks like that is coded within the program because there were no applicable statements in the transaction log.

I tried this statement:

FORWARD TO Testing { SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0};

and I get Record(s) cannot be read; no read permission on 'MSysObjects'.

Google tells me that for that error message you need to open Access and change the security on that table, but the whole point of the exercise is to be able to convert a file using the remote db without having to open it in Access. The permissions sure aren't keeping Sybase Central from getting a table list because that's what it shows when you try to use the wizard to create a proxy table: a list of available tables.

I appreciate all the help.

[MarkCulp · Accepted solution]

Try using sp_remote_tables( ... ) - see the docs for the parameters (e.g. first parameter is the proxy server name)

---

FWIW the transaction log only records the changes made to the database. The SQL that Sybase Central uses to query the database will not appear in the transaction log because these SQL queries do not update the database. In the future you could consider using Request Logging by using the -zr and -zo server command line switches to see what SQL statements are being sent to the server from any client - see the docs for more information.

[former_SQLA_member1694868 · Accepted solution]

Excellent. Thanks Mark!