sp_helpconstraint

Description

Reports information about integrity constraints used in the specified tables.

Syntax

sp_helpconstraint [objname][, detail] 

Parameters

objname

is the name of a table that has one or more integrity constraints defined by a create table or alter table statement.

detail

returns information about the constraint’s user or error messages.

Examples

Example 1

Displays the constraint information for the store_employees table in the pubs3 database. The store_employees table has a foreign key to the stores table (stor_id) and a self-reference (mgr_id references emp_id):

sp_helpconstraint store_employees
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

Example 2

Displays more detailed information about the pubs3..salesdetail constraints, including the constraint type and any constraint error messages:

sp_helpconstraint titles, detail
name                            type
      defn
        msg
------------------------------  ------------------------
     ------------------------------------------------------------
        --------------------------------------------
datedflt                        default value
      create default datedflt as getdate()

typedflt                        default value
      create default typedflt as "UNDECIDED"

titles_pub_id_96003373          referential constraint
      titles FOREIGN KEY (pub_id) REFERENCES publishers(pub_id)
        standard system error message number : 547

roysched_title__144003544       referential constraint
      roysched FOREIGN KEY (title_id) REFERENCES titles(title_id)
        standard system error message number : 547

salesdetai_title__368004342     referential constraint
      salesdetail FOREIGN KEY (title_id) REFERENCES titles(title_id)
        standard system error message number : 547

titleautho_title__432004570     referential constraint
      titleauthor FOREIGN KEY (title_id) REFERENCES titles(title_id)
        standard system error message number : 547

titles_800033162                unique constraint
      UNIQUE INDEX ( title_id) : NONCLUSTERED, FOREIGN REFERENCE
        standard system error message number : 2601

(7 rows affected)

Total Number of Referential Constraints: 4

Details:
-- Number of references made by this table: 1
-- Number of references to this table: 3
-- Number of self references to this table: 0

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.

Example 3

Displays a listing of all tables in the pubs3 database:

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)

Usage

Permissions

Any user can execute sp_helpconstraint. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Commands alter table, create table

System procedures sp_configure, sp_help, sp_helpdb, sp_monitorconfig