sp_foreignkeys (T-SQL)

Returns the foreign keys that reference primary keys on the table in the linked server.

Syntax

sp_foreignkeys [@table_server =] 'table_server'
    [,[@pktab_name =] 'pktab_name']
    [,[@pktab_schema =] 'pktab_schema']
    [,[@pktab_catalog =] 'pktab_catalog']
    [,[@fktab_name =] 'fktab_name']
    [,[@fktab_schema =] 'fktab_schema']
    [,[@fktab_catalog =] 'fktab_catalog']

Arguments

[@table_server =] 'table_server': Is the name of the linked server for which to return table information. table_server is sysname, with no default.
[@pktab_name =] 'pktab_name': Is the name of the table with a primary key. pktab_name is sysname, with a default of NULL.
[@pktab_schema =] 'pktab_schema': Is the name of the schema with a primary key. pktab_schema is sysname, with a default of NULL. In SQL Server, this contains the owner name.
[@pktab_catalog =] 'pktab_catalog': Is the name of the catalog with a primary key. pktab_catalog is sysname, with a default of NULL. In SQL Server, this contains the database name.
[@fktab_name =] 'fktab_name': Is the name of the table with a foreign key. fktab_name is sysname, with a default of NULL.
[@fktab_schema =] 'fktab_schema': Is the name of the schema with a foreign key. fktab_schema is sysname, with a default of NULL.
[@fktab_catalog =] 'fktab_catalog': Is the name of the catalog with a foreign key. fktab_catalog is sysname, with a default of NULL.

Return Code Values

None

Result Sets

Various DBMS products support three-part naming for tables (catalog.schema.table), which is represented in the result set.

Column name	Data type	Description
PKTABLE_CAT	sysname	Catalog for the table in which the primary key resides.
PKTABLE_SCHEM	sysname	Schema for the table in which the primary key resides.
PKTABLE_NAME	sysname	Name of the table (with the primary key). This field always returns a value.
PKCOLUMN_NAME	sysname	Name of the primary key column(s), for each column of the TABLE_NAME returned. This field always returns a value.
FKTABLE_CAT	sysname	Catalog for the table in which the foreign key resides.
FKTABLE_SCHEM	sysname	Schema for the table in which the foreign key resides.
FKTABLE_NAME	sysname	Name of the table (with a foreign key). This field always returns a value.
FKCOLUMN_NAME	sysname	Name of the foreign key column(s), for each column of the TABLE_NAME returned. This field always returns a value.
KEY_SEQ	smallint	Sequence number of the column in a multicolumn primary key. This field always returns a value.
UPDATE_RULE	smallint	Action applied to the foreign key when the SQL operation is an update. SQL Server returns 1 for these columns. Open Data Services gateways can return values of 0, 1, or 2: 0 = CASCADE changes to foreign key. 1 = NO ACTION on changes if foreign key is present. 2 = SET_NULL; set foreign key to NULL.
DELETE_RULE	smallint	Action applied to the foreign key when the SQL operation is a deletion. SQL Server returns 1 for these columns. Open Data Services gateways can return values of 0, 1, or 2: 0 = CASCADE changes to foreign key. 1 = NO ACTION on changes if foreign key is present. 2 = SET_NULL; set foreign key to NULL.
FK_NAME	sysname	Foreign key identifier. It is NULL if not applicable to the data source. SQL Server returns the FOREIGN KEY constraint name.
PK_NAME	sysname	Primary key identifier. It is NULL if not applicable to the data source. SQL Server returns the PRIMARY KEY constraint name.
DEFERRABILITY	smallint	Indicates whether constraint checking is deferrable.

In the result set, the FK_NAME and PK_NAME columns always return NULL.

Remarks

sp_foreignkeys queries the FOREIGN_KEYS rowset of the IDBSchemaRowset interface of the OLE DB provider that corresponds to table_server. The table_name, table_schema, table_catalog, and column parameters are passed to this interface to restrict the rows that are returned.

Examples

This example returns foreign key information about the Customers table in the Northwind database.

USE master

EXEC sp_foreignkeys @table_server = N'LONDON1', @pktab_name = N'Customers', @pktab_catalog = N'Northwind'

sp_catalogs	sp_primarykeys
sp_column_privileges	sp_table_privileges
sp_indexes	sp_tables_ex
sp_linkedservers	System Stored Procedures