sp_helpconstraint reports information about the declarative referential integrity constraints that are specified for a table, including the constraint name and definition of the default, unique or primary key constraint, referential, or check constraint.
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, sp_helpconstraint output on the store_employees table in pubs3 looks similar to:
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:
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)
This report shows that the titles table has the largest number of referential constraints in the pubs3 database.