Reports information about a database object (any object listed in sysobjects) and about system or user-defined datatypes.
sp_help [objname]
is the name of any object in sysobjects or any user-defined datatype or system datatype in systypes. You cannot specify database names. objname can include tables, views, stored procedures, logs, rules, defaults, triggers, referential constraints, and check constraints. Use owner names if the object owner is not the user running the command and is not the Database Owner.
Displays a list of objects in sysobjects and displays each object’s name, owner, and object type. Also displays a list of each user-defined datatype in systypes, indicating the datatype name, storage type, length, null type, default name, and rule name. Null type is 0 (null values not allowed) or 1 (null values allowed):
sp_help
Displays information about the publishers table. sp_help also lists any attributes assigned to the specified table and its indexes, giving the attribute’s class, name, integer value, character value, and comments. The above example shows cache binding attributes for the publishers table:
sp_help publishersName Owner Object_Type -------------------------- --------------------------- ----------- publishers dbo user table (1 row affected) Data_located_on_segment When_created ------------------------------ -------------------------- default Apr 25 2002 10:28AMColumn_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity ---------- ---- ------ ----- ----- ----- ----------- ----------- --------------- ------------------------------ --------
pub_id char 4 NULL NULL 0 NULL pub_idrule NULL 0 pub_name varchar 40 NULL NULL 1 NULL NULL NULL 0 city varchar 20 NULL NULL 1 NULL NULL NULL 0 state char 2 NULL NULL 1 NULL NULL NULL 0 index_name index_description index_keys index_max_rows_per_page index_fillfactor index_reservepagegap index_created ----------- ---------------------------------- ----------------- -------------------------- -------------------- --------------------pubind clustered, unique located on default pub_id 0 0 0 Apr 25 2002 10:28AM (1 row affected) keytype object related_object object_keys related_keys ---------- ------------ ------------------ --------------------- --------------------- primary publishers -- none -- pub_id, *, *, *, *, *, *, * *, *, *, *, *, *, *, * foreign titles publishers pub_id, *, *, *, *, *, *, pub_id, *, *, *, *, *, *, * (1 row affected) Object is not partitioned. Lock scheme Allpages The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme. The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme. exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ------------ -------------- ---------- ----------------- ------------ 0 0 0 0 0 concurrency_opt_threshold ------------------------- 0
Displays information about a partitioned table (in this example, the titles table was first altered to have four partitions):
sp_help titlesName Owner Object_Type -------------------------- --------------------------- ----------- titles dbo user table (1 row affected) Data_located_on_segment When_created ------------------------------ -------------------------- default Apr 25 2002 10:28AMColumn_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity ---------- ---- ------- ----- ----- ----- ------------ --------------- ------------------------------ -------- title_id tid 6 NULL NULL 0 NULL title_idrule NULL 0 title varchar 80 NULL NULL 0 NULL NULL NULL 0 type char 12 NULL NULL 0 typedflt NULL NULL 0 pub_id char 4 NULL NULL 1 NULL NULL NULL 0 price money 8 NULL NULL 1 NULL NULL NULL 0 advance money 8 NULL NULL 1 NULL NULL NULL 0 total_sales int 4 NULL NULL 1 NULL NULL NULL 0 notes varchar 200 NULL NULL 1 NULL NULL NULL 0 pubdate datetime 8 NULL NULL 0 datedflt NULL NULL 0 contract bit 1 NULL NULL 0 NULL NULL NULL 0 index_name index_description index_keys index_max_rows_per_page index_fillfactor index_reservepagegap index_created ----------- ---------------------------------- ----------------- ----------------------- -------------------- ------------------- -------------- titleidind clustered, unique located on default title_id Apr 25 2002 10:28AM titleind nonclustered located on default title Apr 25 2002 10:28AM (2 rows affected) keytype object related_object object_keys related_keys ---------- ------------ ------------------ --------------------- --------------------- foreign roysched titles title_id, *, *, *, *, *, *, * title_id, *, *, *, *, *, *, * foreign salesdetail titles title_id, *, *, *, *, *, *, * title_id, *, *, *, *, *, *, foreign titleauthor titles title_id, *, *, *, *, *, *, * title_id, *, *, *, *, *, *, foreign titles publishers pub_id, *, *, *, *, *, *, * pub_id, *, *, *, *, *, *, * primary titles -- none -- title_id, *, *, *, *, *, *, * *, *, *, *, *, *, *, * (1 row affected) partitionid firstpage controlpage ptn_data_pages ----------- ----------- ---------- -------------- 1 784 785 1 2 713 712 1 3 721 720 1 4 945 944 1 Partitions Average Pages Maximum Pages Minimum Pages Ratio (Max/Avg) ---------- ------------- -------------- ------- ---------- 4 1 1 1 1.000000 Lock scheme Allpages The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme. The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme. exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ------------ -------------- ---------- ----------------- ------------ 0 0 0 0 0 concurrency_opt_threshold ------------------------- 0
Displays information about the trigger marytrig owned by user “mary”. The quotes are needed, because the period is a special character:
sp_help "mary.marytrig"
Name Owner Object_type ------------ ------------------ ---------------- marytrig mary trigger Data_located_on_segment When_created ----------------------- -------------------------- not applicable Mar 20 2002 2:03PM
Displays information about the system datatype money:
sp_help money
Type_name Storage_type Length Prec Scale Nulls Defaul_name --------- ------------ ------- ----- ----- ----- ------------ Rule_name Access_Rule_name Identity --------- ---------------- -------- money money 8 NULL NULL 1 NULL NULL NULL 0
Displays information about the user-defined datatype identype. The report indicates the base type from which the datatype was created, whether it allows nulls, the names of any rules and defaults bound to the datatype, and whether it has the IDENTITY property:
sp_help identype
Type_name Storage_type Length Prec Scale Nulls Defaul_name --------- ------------ ------- ----- ----- ----- ------------ Rule_name Access_Rule_name Identity --------- ---------------- -------- identype numeric 4 NULL NULL 1 NULL NULL NULL 1
sp_help looks for an object in the current database only.
sp_help follows the Adaptive Server rules for finding objects:
If you do not specify an owner name, and you own an object with the specified name, sp_help reports on that object.
If you do not specify an owner name, and do not own an object of that name, but the Database Owner does, sp_help reports on the Database Owner’s object.
If neither you nor the Database Owner owns an object with the specified name, sp_help reports an error condition, even if an object with that name exists in the database for a different owner. Qualify objects that are owned by database users other than yourself and the Database Owner with the owner’s name, as shown in Example 4.
If both you and the Database Owner own objects with the specified name, and you want to access the Database Owner’s object, specify the name in the format dbo.objectname.
sp_help works on temporary tables if you issue it from tempdb.
Columns with the IDENTITY property have an “Identity” value of 1; others have an “Identity” value of 0. In example 2, there are no IDENTITY columns.
sp_help lists any indexes on a table, including indexes created by defining unique or primary key constraints in the create table or alter table statements. It also lists any attributes associated with those indexes. However, sp_help does not describe any information about the integrity constraints defined for a table. Use sp_helpconstraint for information about any integrity constraints.
sp_help displays the following new settings:
The locking scheme, which can be set with create table and changed with alter table
The expected row size, which can be set with create table and changed with sp_chgattribute
The reserve page gap, which can be set with create table and changed with sp_chgattribute
The row lock promotion settings, which can be set or changed with sp_setrowlockpromote and dropped with sp_droprowlockpromote
sp_help includes the report from sp_helpindex, which shows the order of the keys used to create the index and the space management properties.
When Component Integration Services is enabled, sp_help displays information on the storage location of remote objects.
sp_help reports information about SQLJ stored procedures and SQLJ functions. See Java in Adaptive Server Enterprise for more information about SQLJ routines.
Any user can execute sp_help.
Commands create table, alter table
System procedures sp_chgattribute, sp_droprowlockpromote, sp_helpconstraint, sp_helpindex, sp_setrowlockpromote
