sp_fkeys

Description

Returns information about foreign key constraints created with the create table or alter table command in the current database.

Syntax

sp_fkeys pktable_name [, pktable_owner]
	[, pktable_qualifier] [, fktable_name] 
	[, fktable_owner] [, fktable_qualifier]

Parameters

pktable_name: is the name of the primary key table. The use of wildcard characters in pattern matching is not supported. You must specify either the pktable_name or the fktable_name, or both.

pktable_owner: is the name of the primary key table owner. The use of wildcard characters in pattern matching is not supported. If you do not specify the table owner, sp_fkeys looks for a table owned by the current user and then for a table owned by the Database Owner.

pktable_qualifier: is the name of the database that contains the primary key table. This can be either the current database or NULL.

fktable_name: is the name of the foreign key table. The use of wildcard characters in pattern matching is not supported. Either the fktable_name or the pktable_name, or both, must be given.

fktable_owner: is the name of the foreign key table owner. The use of wildcard characters in pattern matching is not supported. If an fktable_owner is not specified, sp_fkeys looks for a table owned by the current user and then for a table owned by the Database Owner.

fktable_qualifier: is the name of the database that contains the foreign key table. This can be either the current database or null.

Usage

sp_fkeys returns information about foreign key constraints created with the create table or alter table command in the current database. A foreign key is a key column in a table that logically depends on a primary key column in another table.

The results set for sp_fkeys is:

Column	Datatype	Description
pktable_qualifier	varchar(32)	The database that contains the primary key table.
pktable_owner	varchar(32)	The owner of the primary key table.
pktable_name	varchar(32)	NOT NULL.
pkcolumn_name	varchar(32)	NOT NULL.
fktable_qualifier	varchar(32)	The database that contains the foreign key table.
fktable_owner	varchar(32)	The owner of the foreign key table.
fktable_name	varchar(32)	NOT NULL.
fkcolumn_name	varchar(32)	NOT NULL.
key_seq	smallint	NOT NULL. The sequence number of the column in a multicolumn primary key.
update_rule	smallint	Action to be applied to the foreign key when the SQL operation is UPDATE. Zero is returned for this column.
delete_rule	smallint	Action to be applied to the foreign key when the SQL operation is DELETE. Zero is returned for this column.

Both the primary key and foreign key must have been declared in a create table or alter table statement.

If the primary key table name is supplied, but the foreign key table name is NULL, sp_fkeys returns all tables that include a foreign key to the given table. If the foreign key table name is supplied, but the primary key table name is NULL, sp_fkeys returns all tables that are related by a primary key/foreign key relationship to foreign keys in the foreign key table.

sp_fkeys does not return information about keys declared with sp_commonkey, sp_foreignkey or sp_primarykey.

Permissions

Any user can execute sp_fkeys.