sp_help

Description

Reports information about a database object (any object listed in sysobjects) and about system or user-defined datatypes.

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

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

Example 3

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

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 

Usage

Permissions

Any user can execute sp_help.

See also

Commands create table, alter table

System procedures sp_chgattribute, sp_droprowlockpromote, sp_helpconstraint, sp_helpindex, sp_setrowlockpromote