Partilhar via

sp_help (Transact-SQL)

  • Artigo

Reports information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type supplied by SQL Server 2005.

Important

This feature has changed from earlier versions of SQL Server. For more information, see Behavior Changes to Database Engine Features in SQL Server 2005.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_help [ [ @objname = ] 'name' ]

Arguments

  • [ @objname=] 'name'
    Is the name of any object, in sysobjects or any user-defined data type in the systypes table. name is nvarchar(776), with a default of NULL. Database names are not acceptable.

Return Code Values

0 (success) or 1 (failure)

Result Sets

The result sets that are returned depend on whether name is specified, when it is specified, and what database object it is.

  1. If sp_help is executed with no arguments, summary information of objects of all types that exist in the current database is returned.

    Column name Data type Description

    Name

    nvarchar(128)

    Object name

    Owner

    nvarchar(128)

    Object owner

    Object_type

    nvarchar(31)

    Object type

  2. If name is a SQL Server data type or user-defined data type, sp_help returns this result set.

    Column name Data type Description

    Type_name

    nvarchar(128)

    Data type name.

    Storage_type

    nvarchar(128)

    SQL Server type name.

    Length

    smallint

    Physical length of the data type (in bytes).

    Prec

    int

    Precision (total number of digits).

    Scale

    int

    Number of digits to the right of the decimal.

    Nullable

    varchar(35)

    Indicates whether NULL values are allowed: Yes or No.

    Default_name

    nvarchar(128)

    Name of a default bound to this type.

    NULL = No default is bound.

    Rule_name

    nvarchar(128)

    Name of a rule bound to this type.

    NULL = No default is bound.

    Collation

    sysname

    Collation of the data type. NULL for non-character data types.

  3. If name is any database object other than a data type, sp_help returns this result set and also additional result sets, based on the type of object specified.

    Column name Data type Description

    Name

    nvarchar(128)

    Table name

    Owner

    nvarchar(128)

    Table owner

    Type

    nvarchar(31)

    Table type

    Created_datetime

    datetime

    Date table created

    Depending on the database object specified, sp_help returns additional result sets.
    If name is a system table, user table, or view, sp_help returns the following result sets. However, the result set that describes where the data file is located on a file group is not returned for a view.

    • Additional result set returned on column objects:

      Column name

    • Additional result set returned on identity columns:

      Column name Data type Description

      Identity

      nvarchar(128)

      Column name whose data type is declared as identity.

      Seed

      numeric

      Starting value for the identity column.

      Increment

      numeric

      Increment to use for values in this column.

      Not For Replication

      int

      IDENTITY property is not enforced when a replication login, such as sqlrepl, inserts data into the table:

      1 = True

      0 = False

    • Additional result set returned on columns:

      Column name Data type Description

      RowGuidCol

      sysname

      Name of the global unique identifier column.

    • Additional result set returned on filegroups:

      Column name Data type Description

      Data_located_on_filegroup

      nvarchar(128)

      Filegroup in which the data is located: Primary, Secondary, or Transaction Log.

    • Additional result set returned on indexes:

      Column name Data type Description

      index_name

      sysname

      Index name.

      Index_description

      varchar(210)

      Description of the index.

      index_keys

      nvarchar(2078)

      Column names on which the index is built.

    • Additional result set returned on constraints:

      Column name Data type Description

      constraint_type

      nvarchar(146)

      Type of constraint.

      constraint_name

      nvarchar(128)

      Name of the constraint.

      delete_action

      nvarchar(9)

      Indicates whether the DELETE action is: No Action, CASCADE, or N/A.

      Only applicable to FOREIGN KEY constraints.

      update_action

      nvarchar(9)

      Indicates whether the UPDATE action is: No Action, Cascade, or N/A.

      Only applicable to FOREIGN KEY constraints.

      status_enabled

      varchar(8)

      Indicates whether the constraint is enabled: Enabled, Disabled, or N/A.

      Only applicable to CHECK and FOREIGN KEY constraints.

      status_for_replication

      varchar(19)

      Indicates whether the constraint is for replication.

      Only applicable to CHECK and FOREIGN KEY constraints.

      constraint_keys

      nvarchar(2078)

      Names of the columns that make up the constraint or, in the case for defaults and rules, the text that defines the default or rule.

      SQL Server 2005 differs from SQL Server 2000 in the way it decodes and stores SQL expressions in the catalog metadata. The semantics of the decoded expression are equivalent to the original text; however, there are no syntactic guarantees. For example, white spaces are removed from the decoded expression. For more information, see, Behavior Changes to Database Engine Features in SQL Server 2005.

    • Additional result set returned on referencing objects:

      Column name Data type Description

      Table is referenced by

      nvarchar(516)

      Identifies other database objects that reference the table.

    • Additional result set returned on stored procedures, functions, or extended stored procedures.

      Column name Data type Description

      Parameter_name

      nvarchar(128)

      Stored procedure parameter name.

      Type

      nvarchar(128)

      Data type of the stored procedure parameter.

      Length

      smallint

      Maximum physical storage length, in bytes.

      Prec

      int

      Precision or total number of digits.

      Scale

      int

      Number of digits to the right of the decimal point.

      Param_order

      smallint

      Order of the parameter.

Remarks

The sp_help procedure looks for an object in the current database only.

When name is not specified, sp_help lists object names, owners, and object types for all objects in the current database. sp_helptrigger provides information about triggers.

Permissions

Requires membership in the public role.

Examples

A. Returning information about all objects

The following example lists information about each object in the master database.

USE master;
GO
EXEC sp_help;
GO

B. Returning information about a single object

The following example displays information about the Contact table.

USE AdventureWorks;
GO
EXEC sp_help 'Person.Contact';
GO

See Also

Reference

Database Engine Stored Procedures (Transact-SQL)
sp_helpgroup (Transact-SQL)
sp_helpindex (Transact-SQL)
sp_helprotect (Transact-SQL)
sp_helpserver (Transact-SQL)
sp_helptrigger (Transact-SQL)
sp_helpuser (Transact-SQL)
System Stored Procedures (Transact-SQL)
sys.sysobjects (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
New content:
  • In the constraint_keys column, added content about how SQL expressions are stored in SQL Server 2005.