Skip to content

Latest commit

 

History

History
136 lines (94 loc) · 8.34 KB

sp-fkeys-transact-sql.md

File metadata and controls

136 lines (94 loc) · 8.34 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sp_fkeys (Transact-SQL)
sp_fkeys returns logical foreign key information for the current environment.
markingmyname
maghan
randolphwest
07/16/2024
sql
system-objects
reference
sp_fkeys
sp_fkeys_TSQL
sp_fkeys
TSQL
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

sp_fkeys (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricdw]

Returns logical foreign key information for the current environment. This procedure shows foreign key relationships including disabled foreign keys.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_fkeys
    [ [ @pktable_name = ] N'pktable_name' ]
    [ , [ @pktable_owner = ] N'pktable_owner' ]
    [ , [ @pktable_qualifier = ] N'pktable_qualifier' ]
    [ , [ @fktable_name = ] N'fktable_name' ]
    [ , [ @fktable_owner = ] N'fktable_owner' ]
    [ , [ @fktable_qualifier = ] N'fktable_qualifier' ]
[ ; ]

Arguments

[ @pktable_name = ] N'pktable_name'

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 isn't supported. This parameter or the @fktable_name parameter, or both, must be supplied.

[ @pktable_owner = ] N'pktable_owner'

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 isn't supported. If @pktable_owner isn't specified, the default table visibility rules of the underlying database management system (DBMS) apply.

In [!INCLUDE ssNoVersion], if the current user owns a table with the specified name, that table's columns are returned. If @pktable_owner isn't specified and the current user doesn't 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 = ] N'pktable_qualifier'

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 [!INCLUDE ssNoVersion], the qualifier represents the database name. In some products, it represents the server name of the table's database environment.

[ @fktable_name = ] N'fktable_name'

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 isn't supported. This parameter or the @pktable_name parameter, or both, must be supplied.

[ @fktable_owner = ] N'fktable_owner'

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 isn't supported. If @fktable_owner isn't specified, the default table visibility rules of the underlying DBMS apply.

In [!INCLUDE ssNoVersion], if the current user owns a table with the specified name, that table's columns are returned. If @fktable_owner isn't specified and the current user doesn't 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 = ] N'fktable_qualifier'

The name of the table (with a foreign key) qualifier. @fktable_qualifier is sysname, with a default of NULL. In [!INCLUDE ssNoVersion], 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 set

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. Possible values:
0 = CASCADE changes to foreign key.
1 = NO ACTION changes if foreign key is present.
2 = SET_NULL
3 = set default
DELETE_RULE smallint Action applied to the foreign key when the SQL operation is a deletion. Possible values:
0 = CASCADE changes to foreign key.
1 = NO ACTION changes if foreign key is present.
2 = SET_NULL
3 = set default
FK_NAME sysname Foreign key identifier. It's NULL if not applicable to the data source. [!INCLUDE ssNoVersion] returns the FOREIGN KEY constraint name.
PK_NAME sysname Primary key identifier. It's NULL if not applicable to the data source. [!INCLUDE ssNoVersion] 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 methods:

  • Temporarily disabling constraint checking (ALTER TABLE NOCHECK or CREATE TABLE NOT FOR REPLICATION) while working with the tables, and then enabling it again later.

  • 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 [!INCLUDE sssampledbobject-md] database.

USE AdventureWorks2022;
GO

EXEC sp_fkeys
    @pktable_name = N'Department',
    @pktable_owner = N'HumanResources';

Examples: [!INCLUDE ssazuresynapse-md] and [!INCLUDE ssPDW]

The following example retrieves a list of foreign keys for the DimDate table in the AdventureWorksPDW2012 database. No rows are returned because [!INCLUDE ssazuresynapse-md] doesn't support foreign keys.

EXEC sp_fkeys @pktable_name = N'DimDate';

Related content