sp_ha_admin  sp_help_resource_limit

Chapter 1: System Procedures

sp_help

Description

Reports information about a database object (any object listed in sysobjects) and about system or user-defined datatypes, as well as 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, stored procedures, logs, rules, defaults, triggers, referential constraints, 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

Usage

Permissions

Any user can execute sp_help.

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 sp_help reports information about SQLJ stored procedures and SQLJ functions. See 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





Copyright © 2005. Sybase Inc. All rights reserved. sp_help_resource_limit

View this book as PDF