sp_remote_tables system procedure

Returns a list of the tables on a server.

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 remote server name.
@table_name – Use this optional CHAR(128) parameter to specify the name of the remote table. The default is '%'.
@table_owner – Use this optional CHAR(128) parameter to specify the owner of the remote table. The default is '%'.
@table_qualifier – Use this optional CHAR(128) parameter to specify the database in which table_name is located. The default is '%'.
@with_table_type – Use this optional BIT parameter to specify the inclusion of remote table types. The default is 0. Specify 1 if you want the result set to include a column that lists table types or specify 0 if you do not.

Result set

Column name	Data type	Description
database	CHAR(128)	The name of the remote database.
owner	CHAR(128)	The name of the table owner.
table_name	CHAR(128)	The name of the 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

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

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.

Privileges

None

Side effects

None

Standards and compatibility

Sybase – Supported by Open Client/Open Server.

Examples

The following example returns information about the tables owned by DBA in a SAP Sybase IQ remote server named RemoteSA.

CALL sp_remote_tables( 'RemoteSA', null, 'DBA' );

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

CALL sp_remote_tables( 'RemoteASE', null, 'Fred', 'production' );

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

CALL sp_remote_tables( 'RemoteExcel' );