sp_remote_tables system procedure

Returns a list of the tables on a server.

The server must be defined with the CREATE SERVER statement to use this system procedure.

Syntax

sp_remote_tables(
 server_name
 [, table_name
 [, table_owner
 [, table_qualifier
 [, with_table_type ] ] ] ]
)

Arguments

server_name – Use this CHAR(128) parameter to specify the server the remote table is located on.
table_name – Use this optional CHAR(128) parameter to specify the remote table.
table_owner – Use this optional CHAR(128) parameter to specify the owner of the remote table.
table_qualifier – Use this optional CHAR(128) parameter to specify the database in which table_name is located.
with_table_type – Use this optional BIT parameter to specify the type of remote table. This argument is a bit type and accepts two values, 0 (the default) and 1. You must enter the value 1 if you want the result set to include a column that lists table types.

Result set

Column name	Data type	Description
database	CHAR(128)	The name of the remote database.
owner	CHAR(128)	The name of the remote database owner.
table-name	CHAR(128)	The remote table.
table-type	CHAR(128)	Specifies the table type. The value depends on the type of remote server. For example, TABLE, VIEW, SYS, and GBL TEMP are possible values.

Remarks

It may be helpful when you are configuring your database server to get a list of the remote tables available on a particular server. This procedure returns a list of the tables on a server.

The procedure accepts five parameters. If a table, owner, or database name is given, the list of tables will be limited to only those that match the arguments.

Standards and compatibility

Sybase – Supported by Open Client/Open Server.

Permissions

None

Side effects

None

Examples

To get a list of all the Microsoft Excel worksheets available from an ODBC data source referenced by a server named excel:

CALL sp_remote_tables( 'excel' );

To get a list of all the tables owned by fred in the production database in an Adaptive Server Enterprise server named asetest:

CALL sp_remote_tables( 'asetest', null, 'fred', 'production' );