sp_fkeys (Transact-SQL)
Returns logical foreign key information for the current environment. This procedure shows foreign key relationships including disabled foreign keys.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_fkeys [ @pktable_name = ] 'pktable_name'
[ , [ @pktable_owner = ] 'pktable_owner' ]
[ , [ @pktable_qualifier = ] 'pktable_qualifier' ]
{ , [ @fktable_name = ] 'fktable_name' }
[ , [ @fktable_owner = ] 'fktable_owner' ]
[ , [ @fktable_qualifier = ] 'fktable_qualifier' ]
Arguments
- [ @pktable_name = ] 'pktable_name'
Is the name of the table, with the primary key, used to return catalog information. pktable_name is sysname, with a default of NULL. Wildcard pattern matching is not supported. This parameter or the fktable_name parameter, or both, must be supplied.
[ @pktable_owner = ] 'pktable_owner'
Is the name of the owner of the table (with the primary key) used to return catalog information. pktable_owner is sysname, with a default of NULL. Wildcard pattern matching is not supported. If pktable_owner is not specified, the default table visibility rules of the underlying DBMS apply.If @pktable_owner is not specified, only the dbo schema will be searched.
- [ @pktable_qualifier = ] 'pktable_qualifier'
Is the name of the table (with the primary key) qualifier. pktable_qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, the qualifier represents the database name. In some products, it represents the server name of the table's database environment.
- [ @fktable_name = ] 'fktable_name'
Is the name of the table (with a foreign key) used to return catalog information. fktable_name is sysname, with a default of NULL. Wildcard pattern matching is not supported. This parameter or the pktable_name parameter, or both, must be supplied.
[ @fktable_owner = ] 'fktable_owner'
Is the name of the owner of the table (with a foreign key) used to return catalog information. fktable_owner is sysname, with a default of NULL. Wildcard pattern matching is not supported. If fktable_owner is not specified, the default table visibility rules of the underlying DBMS apply.If @fktable_owner is not specified, only the dbo schema will be searched.
- [ @fktable_qualifier =] 'fktable_qualifier'
Is the name of the table (with a foreign key) qualifier. fktable_qualifier is sysname, with a default of NULL. In SQL Server, the qualifier represents the database name. In some products, it represents the server name of the table's database environment.
Return Code Values
None
Result Sets
| Column name | Data type | Description |
|---|---|---|
PKTABLE_QUALIFIER |
sysname |
Name of the table (with the primary key) qualifier. This field can be NULL. |
PKTABLE_OWNER |
sysname |
Name of the table (with the primary key) owner. This field always returns a value. |
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 columns, for each column of the TABLE_NAME returned. This field always returns a value. |
FKTABLE_QUALIFIER |
sysname |
Name of the table (with a foreign key) qualifier. This field can be NULL. |
FKTABLE_OWNER |
sysname |
Name of the table (with a foreign key) owner. This field always returns a value. |
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, 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 or 1 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 or 1 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 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. |
The results returned are ordered by FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, and KEY_SEQ.
Remarks
Application coding that includes tables with disabled foreign keys can be implemented by the following:
- Temporarily disabling constraint checking (ALTER TABLE NOCHECK or CREATE TABLE NOT FOR REPLICATION) while working with the tables, and then enabling it again later. For more information about the NOT FOR REPLICATION option, see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.
- Using triggers or application code to enforce relationships.
If the primary key table name is supplied and 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 and the primary key table name is NULL, sp_fkeys returns all tables related by a primary key/foreign key relationship to foreign keys in the foreign key table.
The sp_fkeys stored procedure is equivalent to SQLForeignKeys in ODBC.
Permissions
Requires SELECT permission on the schema.
Examples
The following example retrieves a list of foreign keys for the HumanResources.Department table in the AdventureWorks database.
USE AdventureWorks;
GO
EXEC sp_fkeys @pktable_name = N'Department',
@pktable_owner = N'HumanResources'
See Also
Reference
Catalog Stored Procedures (Transact-SQL)
System Stored Procedures (Transact-SQL)
sp_pkeys (Transact-SQL)