sp_iqconstraint Procedure

Lists referential integrity constraints defined using CREATE TABLE or ALTER TABLE for the specified table or column.

Syntax

sp_iqconstraint ['table-name', 'column-name', 'table-owner' ]

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Description

If table name and column name are omitted, reports all referential integrity constraints for all tables including temporary ones in the current connected database. The information includes unique or primary key constraint, referential constraint, and associated role name that are defined by the CREATE TABLE and/or ALTER TABLE statements.

Example

This is sample output that displays all primary key/foreign key pairs where either the candidate key or foreign key contains column ck1 for owner bob in all tables:

call sp_iqconstraint('','ck1','bob')

PTAB1 bob ASIQ_IDX_T27_HG  unique   ck1,ck2  selftab bob CK6FK3  Y  
ASIQ_IDX_T42_HG  ck1,ck2PTAB2 bob ASIQ_IDX_T27_HG  unique   ck1,ck2  selftab bob CK6FK4  Y  
ASIQ_IDX_T206_I42_HG  ck1,ck2selftab bob ASIQ_IDX_T26_HG  unique   ck1,ck2  selftab bob CK3FK1  Y  
ASIQ_IDX_T206_I42_HG  ck1,ck2

The columns displayed are:

Primary enforced table
Table owner
Candidate key index
Primary key or Unique
Primary key columns
Foreign table
Foreign table owner
Foreign key role name
Enforced status (“Y” for enforced, “N” for unenforced)
Foreign key index
Foreign key columns
Location (“TEMP,” “MAIN,” or “SYSTEM”)