sp_fkeys Catalog Stored Procedure

For SQL Server 6.5 information, see sp_fkeysin What's New for SQL Server 6.5.

Returns logical foreign key information for the current environment.

Syntax

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

where

pktable_name: Specifies the table (with the primary key) used to return catalog information. The pktable_name can be a variable character name with a maximum 32 characters. Wildcard pattern matching is not supported. This parameter or the fktable_name parameter, or both, must be supplied.
pktable_owner: Is the name of the owner of the table (with the primary key) used to return catalog information. The pktable_owner parameter can be a variable character name with a maximum 32 characters. Wildcard pattern matching is not supported. If pktable_owner is not specified, the default table visibility rules of the underlying DBMS apply.
In Microsoft SQL Server, if the current user owns a table with the specified name, that table's columns are returned. If the pktable_owner is not specified and the current user does not own a table with the specified pktable_name, the procedure looks for a table with the specified pktable_name owned by the database owner. If one exists, that table's columns are returned.
pktable_qualifier: Is the name of the table (with the primary key) qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.
fktable_name: Specifies the name of the table (with a foreign key) used to return catalog information. The fktable_name can be a variable character name with a maximum of 32 characters. Wildcard pattern matching is not supported. This parameter or the pktable_name parameter, or both, must be supplied.
fktable_owner: Is the name of the owner of the table (with a foreign key) used to return catalog information. The fktable_owner can be a variable character name with a maximum of 32 characters. Wildcard pattern matching is not supported. If fktable_owner is not specified, the default table visibility rules of the underlying DBMS apply.
In Microsoft SQL Server, if the current user owns a table with the specified name, that table's columns are returned. If fktable_owner is not specified and the current user does not own a table with the specified fktable_name, the procedure looks for a table with the specified fktable_name owned by the database owner. If one exists, that table's columns are returned.
fktable_qualifier: Specifies the name of the table (with a foreign key) qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.

Remarks

If the primary key table name is supplied and the foreign key table name is NULL, this procedure returns all tables that include a foreign key to the given table. If the foreign key table name is supplied and the primary key table name is NULL, the procedure returns all tables related by a primary key/foreign key relationship to foreign keys in the foreign key table. Note that this procedure does not support the common key type as specified in the SQL Server syskeys system table.

The sp_fkeys stored procedure is equivalent to SQLForeignKeys in ODBC. The results returned are ordered by FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, and KEY_SEQ.

This is the results set:



Column	Datatype	Description

PKTABLE_QUALIFIER	varchar(32)	Is the name of the table (with the primary key) qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name for the table with a PRIMARY KEY constraint. In some products, it represents the server name of the table's database environment. This field can be NULL.
PKTABLE_OWNER	varchar(32)	Is the name of the table (with the primary key) owner. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the name of the database user that created the table (with a PRIMARY KEY constraint). This field will always return a value.
PKTABLE_NAME	varchar(32)	Is the name of the table (with the primary key). Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the table name (with a PRIMARY KEY constraint) as listed in the sysobjects table. This field will always return a value.
PKCOLUMN_NAME	varchar(32)	Is the name of the primary key column(s), for each column of the TABLE_NAME returned. In Microsoft SQL Server, this column represents the column name as listed in the syscolumns table. This field will always return a value.
FKTABLE_QUALIFIER	varchar(32)	Is the name of the table (with a foreign key) qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name for the table (with a FOREIGN KEY constraint). In some products, it represents the server name of the table's database environment. This field can be NULL.
FKTABLE_OWNER	varchar(32)	Is the name of the table (with a foreign key) owner. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the name of the database user that created the table (with a FOREIGN KEY constraint). This field will always return a value.
FKTABLE_NAME	varchar(32)	Is the name of the table (with a foreign key). Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the table name as listed in the sysobjects table (with a FOREIGN KEY constraint). This field will always return a value.
FKCOLUMN_NAME	varchar(32)	Is the name of the foreign key column(s), for each column of the TABLE_NAME returned. In Microsoft SQL Server, this column represents the column name as listed in the syscolumns table. This field will always return a value.
KEY_SEQ	smallint	Is the sequence number of the column in a multicolumn primary key. This field will always return a value.
UPDATE_RULE	smallint	Is the action applied to the foreign key when the SQL operation is UPDATE. SQL Server returns 1 for these columns. Open Data Services gateways can return non-null values of 0, 1, or 2: 0 CASCADE changes to foreign key. 1 RESTRICT changes if foreign key is present. 2 SET_NULL; set foreign key to NULL.
DELETE_RULE	smallint	Is that action applied to the foreign key when the SQL operation is DELETE. SQL Server returns 1 for these columns. Open Data Services gateways can return non-null values of 0, 1, or 2: 0 CASCADE changes to foreign key. 1 RESTRICT changes if foreign key is present. 2 SET_NULL; set foreign key to NULL.
FK_NAME	varchar(128)	Is a foreign key identifier. It is NULL if not applicable to the data source. SQL Server 6.0 returns the FOREIGN KEY constraint name.
PK_NAME	varchar(128)	It is a primary key identifier. It is NULL if not applicable to the data source. SQL Server 6.0 returns the PRIMARY KEY constraint name.