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

Examples

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.

See also:
  • alter table, create table in Reference Manual: Commands

  • Java in Adaptive Server Enterprise for more information about SQLJ routines.

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:

InformationValues
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

Related reference
sp_chgattribute
sp_droprowlockpromote
sp_helpartition
sp_helpcomputedcolumn
sp_helpconstraint
sp_helpindex
sp_setpglockpromote