Returns logical foreign key information for the current environment. This procedure shows foreign key relationships including disabled foreign keys.
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']
In Microsoft® SQL Server™, if the current user owns a table with the specified name, that table’s columns are returned. If 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.
In 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.
None
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 column(s), 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 | varchar(32) | 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 = RESTRICT 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 = RESTRICT 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.
Application coding that includes tables with disabled foreign keys can be implemented by:
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. 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.
Execute permissions default to the public role.
This example retrieves a list of foreign keys for the Customers table in the Northwind database.
USE Northwind
EXEC sp_fkeys @pktable_name = N'Customers'