sp_help

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

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

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:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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