Using sp_helpconstraint to find a table’s constraint information

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.

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.