sp_remote_columns system procedure

Produces a list of the columns in a remote table, and a description of their data types.

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

Syntax

sp_remote_columns(
@server_name
, @table_name
[, @table_owner
[, @table_qualifier ] ]
)

Arguments

@server_name – Use this CHAR(128) parameter to specify a string containing the server name as specified by the CREATE SERVER statement.
@table_name – Use this CHAR(128) parameter to specify the name of the remote table.
@table_owner – Use this optional CHAR(128) parameter to specify the owner of table_name. The default is '%'.
@table_qualifier – Use this optional CHAR(128) parameter to specify the name of the database in which table_name is located. The default is '%'.

Result set

Column name	Data type	Description
database	CHAR(128)	The database name.
owner	CHAR(128)	The database owner name.
table_name	CHAR(128)	The table name.
column_name	CHAR(128)	The name of a column.
domain_id	SMALLINT	An INTEGER which indicates the data type of the column.
width	INTEGER	The meaning of this column depends on the data type. For character types width represents the number of characters.
scale	SMALLINT	The meaning of this column depends on the data type. For NUMERIC data types scale is the number of digits after the decimal point.
nullable	SMALLINT	If NULL column values are allowed, the value is 1. Otherwise the value is 0.
base_type_str	CHAR(4096)	The annotated type string representing the physical type of the column.

Remarks

If you are entering a CREATE EXISTING TABLE statement and you are specifying a column list, it may be helpful to get a list of the columns that are available on a remote table. sp_remote_columns produces a list of the columns on a remote table and a description of their data types. If you specify a database, you must either specify an owner or provide the value NULL.

Privileges

None

Side effects

None

Standards and compatibility

Sybase – Supported by Open Client/Open Server.

Example

The following example returns information about the columns in the ULProduct table in the remote SAP Sybase IQ database server named RemoteSA. The table owner is DBA.

CALL sp_remote_columns( 'RemoteSA', 'ULProduct', 'DBA', null );

The following example returns information about the columns in the SYSOBJECTS table in the Adaptive Server Enterprise database Production using the remote server named RemoteASE. The table owner is unspecified.

CALL sp_remote_columns( 'RemoteASE', 'sysobjects', null, 'Production' );

The following example returns formation about the columns in the Customers table in the Microsoft Access database c:\users\me\documents\MyAccesDB.accdb using the remote server MyAccessDB. The Access database does not have a table owner so NULL is specified.

CALL sp_remote_columns( 'MyAccessDB', 'Customers', null, 'c:\\users\\me\\documents\\MyAccesDB.accdb' );