sp_remote_imported_keys system procedure

Provides information about remote tables with primary keys that correspond to a specified foreign key.

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

Syntax
sp_remote_imported_keys( 
@server_name
 , @sp_name
 [, @sp_owner
 [, @sp_qualifier ] ]
)
Arguments
  • @server_name   Use this optional CHAR(128) parameter to specify the server the foreign key table is located on. A value is required for this parameter.

  • @sp_name   Use this optional CHAR(128) parameter to specify the table containing the foreign key. A value is required for this parameter.

  • @sp_owner   Use this optional CHAR(128) parameter to specify the foreign key table's owner.

  • @sp_qualifier   Use this optional CHAR(128) parameter to specify the database containing the foreign key table.

Result set
Column name Data type Description
pk_database CHAR(128) The database containing the primary key table.
pk_owner CHAR(128) The owner of the primary key table.
pk_table CHAR(128) The primary key table.
pk_column CHAR(128) The name of the primary key column.
fk_database CHAR(128) The database containing the foreign key table.
fk_owner CHAR(128) The foreign key table's owner.
fk_table CHAR(128) The foreign key table.
fk_column CHAR(128) The name of the foreign key column.
key_seq SMALLINT The key sequence number.
fk_name CHAR(128) The foreign key name.
pk_name CHAR(128) The primary key name.
Remarks

Foreign keys reference a row in a separate table that contains the corresponding primary key. This procedure allows you to obtain a list of the remote tables with primary keys that correspond to a particular foreign table. The sp_remote_imported_keys result set includes the database, owner, table, column, and name for both the primary and the foreign key, and the foreign key sequence for the foreign key columns. The result set may vary because of the underlying ODBC and JDBC calls, but information about the table and column for a primary key is always returned.

Permissions

None

Side effects

None

See also
Example

To get information about the tables with primary keys that correspond to a foreign key on the SYSOBJECTS table in the asetest server:

CALL sp_remote_imported_keys(
     @server_name='asetest',
     @sp_name='sysobjects',
     @sp_qualifier='production' );