sp_helpconstraint reports information about the declarative referential integrity constraints specified for a table, including the constraint name and definition of the default, unique or primary key constraint, referential, or check constraint. sp_helpconstraint also reports the number of references associated with the specified tables.
Its syntax is:
sp_helpconstraint [objname] [, detail]
objname is the name of the table being queried. If you do not include a table name, sp_helpconstraint displays the number of references associated with each table in the current database. With a table name, sp_helpconstraint reports the name, definition, and number of integrity constraints associated with the table. The detail option also returns information about the constraint’s user or error messages.
For example, suppose you run sp_helpconstraint on the store_employees table in pubs3.
name defn
--------------------------- --------------------------------
store_empl_stor_i_272004000 store_employees FOREIGN KEY
(stor_id) REFERENCES stores(stor_id)
store_empl_mgr_id_288004057 store_employees FOREIGN KEY
(mgr_id) SELF REFERENCES
store_employees(emp_id)
store_empl_2560039432 UNIQUE INDEX( emp_id) :
NONCLUSTERED, FOREIGN REFERENCE
(3 rows affected)
Total Number of Referential Constraints: 2
Details:
-- Number of references made by this table: 2
-- Number of references to this table: 1
-- Number of self references to this table: 1
Formula for Calculation:
Total Number of Referential Constraints
= Number of references made by this table
+ Number of references made to this table
- Number of self references within this table
To find the largest number of referential constraints associated with any table in the current database, run sp_helpconstraint without specifying a table name, for example:
sp_helpconstraint
id name Num_referential_constraints ----------- ------------------------ --------------------------- 80003316 titles 4 16003088 authors 3 176003658 stores 3 256003943 salesdetail 3 208003772 sales 2 336004228 titleauthor 2 896006223 store_employees 2 48003202 publishers 1 128003487 roysched 1 400004456 discounts 1 448004627 au_pix 1 496004798 blurbs 1 (11 rows affected)
In this report, the titles table has the largest number of referential constraints in the pubs3 database.
