Reports information about a database object (any object listed in sysobjects) and about system or user-defined datatypes, as well as user-defined functions, computed columns and function-based indexes. Column displays optimistic_index_lock.
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, precomputed result sets, stored procedures, logs, rules, defaults, triggers, referential constraints, encryption keys, predicates, and check constraints, but refers to tables when you enable optimistic_index_lock. 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 a partitioned 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.
sp_help publishersName Owner Object_Type Create_date --------------------- --------------- ----------- ----------------- publishers dbo user table Oct 7 2005 11:14AM Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity ---------- ---- ------ ----- ----- ----- ------------ ----------- ---------------- ----------------------- -------- pub_id char 4 NULL NULL 0 NULL pub_idrule NULL 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 Object does not have any indexes. keytype object related_objs object_keys related_keys ------- ------ ------------ ----------- --------------- primary publishers -- none -- pub_id,*,*,*,*,*,*,* *,*,*,*,*,*,*,* name type partition_type partitions partition_keys ---------- -------- -------------- ---------- -------------- publishers base table roundrobin 3 NULL partition_name partition_id pages segment create_date ------------------ -------------- ----- ------- ----------------- publishers_608002166 608002166 1 default Oct 13 2005 11:18AM publishers_1116527980 1116527980 1 default Oct 13 2005 11:18AM publishers_1132528037 1132528037 1 default Oct 13 2005 11:19AM Partition_Conditions -------------------- NULL Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg) --------- --------- --------- -------------- -------------- 1 1 1 1.0000000 1.0000000 Lock scheme Allpages The attribute ’exp_row_size" is not applicable to tables with allpages lock scheme. exp_row reservepagegap fillfactor max_rows_per_page identity_gap ------- -------------- ---------- ----------------- ------------ 0 0 0 0 0 concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg ------------------------- --------------------- ------------------- 0 0 0
Displays information about a partitioned titles table:
sp_help titlesName Owner Object_Type Create_date
---------------- --------------- ----------- ---------------------
titles db user table Oct 7 2005 11:14AM
(1 row affected)Column_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
Object has the following indexes
index_name index_keys index_description index_max_rows_per_page
index_fillfactor index_reservepagegap index_created index_local
---------- ---------- ----------------- ------------------------
---------------- --------------------- ------------ ----------
title_idx total_sales clustered 0
0 0 Oct 13 2005 5:20PM Local Index
index_ptn_name index_ptn_seg
-------------------- ---------------
p1 default
p2 default
p3 default
title_idx_98505151 default
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, *, *, *, *, *, *, *
*, *, *, *, *, *, *, *
name type partition_type partitions partition_keys
---- ---------- -------------- ---------- --------------
titles base table range 4 pubdate
partition_name partition_id pages segment create_date
-------------- ------------ ----- ------- -------------------
q1 937051343 1 default Oct 13 2005 5:20PM
q2 953051400 1 default Oct 13 2005 5:20PM
q3 969051457 1 default Oct 13 2005 5:20PM
q4 985051514 1 default Oct 13 2005 5:20PM
Partition_Conditions
--------------------
VALUES <= ("3/31/2006")
VALUES <= ("6/30/2006")
VALUES <= ("9/30/2006")
VALUES <= ("12/31/2006")
VALUES <= ("3’31’2006")
Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg)
--------- --------- --------- -------------- --------------
1 1 1 1.000000 1.000000
Lock scheme Allpages
The attribute ’exp_row_size" is not applicable to tables with allpages lock scheme.
exp_row reservepagegap fillfactor max_rows_per_page identity_gap
------- -------------- ---------- ----------------- ------------
0 0 0 0 0
concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
0 0 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
Shows a new column, indicating whether optimistic index locking is enabled. 1 indicates that the option is enabled; 0 indicates that it is not.
sp_help "mytable"
------------ exp_row_size reserve pagegap fillfactor max_rows_per_page ------------------------------------------------------ 1 0 0 0 0 concurrency_opt_threshold optimistic_index_lock ------------------------------------------------- 0 1
Shows a virtual computed column:
alter table authors add fullname as au_fname + ' ' + au_lname sp_help authors
Object has the following computed columns Column_Name Property ----------- -------- fullname virtual Text ------------------------------ AS au_fname + ' ' + au_lname
Shows a virtual computed column to a materialized computed column:
alter table authors modify fullname materialzied sp_help authors
Object has the following computed columns Column_Name Property ----------- ------------ fullname materialized Text ------------------------------------------- AS au_fname + ' ' + au_lname MATERIALIZED
The result set for sp_help table_name includes
the Decrypt_Default_name column,
which indicates the decrypt default name for the column. For example,
if you run the following:
create table encr_table(col1 int encrypt decrypt_default 1)
When you run sp_help on encr_table, it shows the following:
Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity Encrypted Decrypt_Default_name ----------- ---- ------ ---- ----- ----- --------------------- -------------- ------------------- --------- ------------------------- c1 int 4 NULL NULL 0 NULL NULL NULL NULL 0 1 encr_table_col1_1036527695
Displays the Name, Owner, Object_type, Object_status, and Create_date of the predicate object:
grant select on tab1 where col1 = 5 as pred1 to robert sp_help pred1
Name Owner Object_type Object_status Create_date ----- ------ ------------- -------------- ------------ pred1 dbo predicate -- none -- Feb 9 2010 12:49PM
For this precomputed result set:
create table numtrips (source int, destination int, count_trip int) create precomputed result set frequent_trips unique (source, destination) as select * from numtrips where count_trip > 100
sp_help numtrips returns the following:
Name Owner Object_type Object_status Create_date ---------------- ---------- ---------------------- ------------------------------------------------------------ -------------------------------------- numtrips dbo user table precomputed result set defined May 11 2012 6:46AM . . .
sp_help frequent_trips returns:
Name Owner Object_type Object_status Create_date ---------------------------- ---------- -------------------------------------------- ---------------------------------------------------------------------- -------------------------------------- frequent_trips dbo precomputed result set immediate, enabled, enabled for QRW May 11 2012 6:46AM . . .
sp_help will display execute as owner or execute as caller in the Object status field as follows:
create proc p1 with execute as owner asselect 1gosp_help p1Name Owner Object_type Object_statuse Create_date ---- ----- ----------- -------------- ----------- p1 dbo stored procedureexecute as ownerJun 8 2012 10:05AM (1 row affected)Column_name Type Length Prec Scale Nulls Not_compressed Default_name Rule _name Access_Rule_name Computed_Column_object Identity --------------------------------------------------------------------------(return status = 0) Rule_name
For virtually-hashed table, sp_help reports:
That a table is virtually-hashed with this message:
Object is Virtually Hashed
The hash_key_factors for the table with a message using this syntax:
column_1:hash_factor_1, column_2:hash_factor_2..., max_hash_key=max_hash_value
For example:
attribute_class attribute int_value char_value comments --------------------- --------------- ------------------------ -------------------------------------- ----------- hash clustered tables hash key factors NULL id:10.0, id2:1.0, max_hash_key=1000.0 NULL
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 displays information about precomputed result set objects in the Object_type column. Adaptive Server treats precomputed result set objects internally as user tables. When you issue sp_help with a precomputed result set as the objectname, it reports all the relevant details about columns, partitions, keys, indexes, and so on, similar to when you run sp_help against a user table.
Additionally, the Object_Status column returns the following for precomputed result sets:
For user tables – returns precomputed
result set defined in the Object_Status column
if any precomputed result set objects are defined on the user table
For precomputed result set objects – returns the following in the Object_Status column for:
The refresh mode – immediate or manual
The precomputed result set state – enabled or disabled
The query rewrite state – enable
for QRW or disabled for QRW
See Example 11, above.
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_setpglockpromote and dropped with sp_droprowlockpromote
sp_help includes the report from:
sp_helpindex – showing the order of the keys used to create the index and the space management properties
sp_helpartition – showing the partition information of the table
sp_helpcomputedcolumn – showing the computed column information of the table
When Component Integration Services is enabled, sp_help displays information on the storage location of remote objects.
sp_help displays information about encryption keys. When a key name is specified as the parameter to sp_help, the command lists the key’s name, owner, object type, and creation date.
sp_help tablename indicates if a column is encrypted, including the name of the decrypt default on the column, if one exists.
sp_help predicate_name displays information about the predicated privilege.
Any user can execute sp_help. Permission checks do not differ based on the granular permissions settings.
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 |
|
Documents Java in Adaptive Server Enterprise for more information about SQLJ routines.
Commands alter table, create table
System procedures sp_chgattribute, sp_droprowlockpromote, sp_helpartition, sp_helpcomputedcolumn, sp_helpconstraint, sp_helpindex, sp_setpglockpromote
