Reports information about integrity constraints used in the specified tables.
sp_helpconstraint [objname][, detail]
is the name of a table that has one or more integrity constraints defined by a create table or alter table statement.
returns information about the constraint’s user or error messages.
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
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.
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)
sp_helpconstraint truncates foreign keys and reference keys to 30 characters.
sp_helpconstraint prints the name and definition of the integrity constraint, and the number of references used by the table. The detail option returns information about the constraint’s user or error messages.
Running sp_helpconstraint with no parameters lists all the tables containing references in the current database, and displays the total number of references in each table. sp_helpconstraint lists the tables in descending order, based on the number of references in each table.
sp_helpconstraint reports only the integrity constraint information about a table (defined by a create table or alter table statement). It does not report information about rules, triggers, or indexes created using the create index statement. Use sp_help to see information about rules, triggers, and indexes for a table.
For constraints that do not have user-defined messages, Adaptive Server reports the system error message associated with the constraint. Query sysmessages to obtain the actual text of that error message.
You can use sp_helpconstraint only for tables in the current database.
If a query exceeds the configured number of auxiliary scan descriptors, Adaptive Server returns an error message. You can use sp_helpconstraint to determine the necessary number of scan descriptors. See the System Administration Guide or more information on the number of aux scan descriptors configuration parameter.
A System Security Officer can prevent the source text of constraint definitions from being displayed to most users who execute sp_helpconstraint. To restrict select permission on the text column of the syscomments table to the object owner or a System Administrator, use sp_configure to set the select on syscomments.text column parameter to 0. This restriction is required to run Adaptive Server in the evaluated configuration. See the System Administration Guide for more information about the evaluated configuration.
Any user can execute sp_helpconstraint.
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 |
|
Commands alter table, create table
System procedures sp_configure, sp_help, sp_helpdb, sp_monitorconfig
