sp_help

Description

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.

Syntax

sp_help [objname]

Parameters

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.

Examples

Example 1

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

Example 2

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

Example 3

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

Example 4

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

Example 5

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 

Example 6

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 

Example 7

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

Example 8

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

Example 9

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

Example 10

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

Example 11

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

Example 12

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                    
. . . 

Example 13

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

Usage

Permissions

Any user can execute sp_help. 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

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