sp_foreignkeys (Transact-SQL)
Applies to: SQL Server
Returns the foreign keys that reference primary keys on the table in the linked server.
Transact-SQL syntax conventions
sp_foreignkeys
[ @table_server = ] N'table_server'
[ , [ @pktab_name = ] N'pktab_name' ]
[ , [ @pktab_schema = ] N'pktab_schema' ]
[ , [ @pktab_catalog = ] N'pktab_catalog' ]
[ , [ @fktab_name = ] N'fktab_name' ]
[ , [ @fktab_schema = ] N'fktab_schema' ]
[ , [ @fktab_catalog = ] N'fktab_catalog' ]
[ ; ]
The name of the linked server for which to return table information. @table_server is sysname, with no default.
The name of the table with a primary key. @pktab_name is sysname, with a default of NULL
.
The name of the schema with a primary key. @pktab_schema is sysname, with a default of NULL
. In SQL Server, this parameter contains the owner name.
The name of the catalog with a primary key. @pktab_catalog is sysname, with a default of NULL
. In SQL Server, this parameter contains the database name.
The name of the table with a foreign key. @fktab_name is sysname, with a default of NULL
.
The name of the schema with a foreign key. @fktab_schema is sysname, with a default of NULL
.
The name of the catalog with a foreign key.@fktab_catalog is sysname, with a default of NULL
.
None.
Various database management system (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 or columns, 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 columns, 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 0, 1, or 2 for these columns:0 = CASCADE changes to foreign key.1 = NO ACTION 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 0, 1, or 2 for these columns:0 = CASCADE changes to foreign key.1 = NO ACTION changes if foreign key is present.2 = SET_NULL ; set foreign key to NULL . |
FK_NAME |
sysname | Foreign key identifier. It's NULL if not applicable to the data source. SQL Server returns the FOREIGN KEY constraint name. |
PK_NAME |
sysname | Primary key identifier. It's 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
.
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 returned.
Requires SELECT
permission on the schema.
The following example returns foreign key information about the Department
table in the AdventureWorks2022
database on the linked server, Seattle1
.
EXEC sp_foreignkeys @table_server = N'Seattle1',
@pktab_name = N'Department',
@pktab_catalog = N'AdventureWorks2022';