Reports information about a database object (any object listed in the sysobjects table), a user-defined data type, or a data type supplied by Microsoft® SQL Server™.
sp_help [[@objname =] name]
0 (success) or 1 (failure)
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 |
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, if no default is bound. |
| Rule_name | nvarchar(128) | Name of a rule bound to this type. NULL, if no default is bound. |
If name is any database object (other than a date type), sp_help returns this result set, as well as 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 these result sets (except the result set describing where the data file is located on a file group is not returned for a view).
| Column name | Data type | Description |
|---|---|---|
| Column_name | nvarchar(128) | Column name. |
| Type | nvarchar(128) | Column data type. |
| Computed | varchar(35) | Indicates whether the values in the column are computed: (Yes or No). |
| Length | int | Column length in bytes. |
| Prec | char(5) | Column precision. |
| Scale | char(5) | Column scale. |
| Nullable | varchar(35) | Indicates whether NULL values are allowed in the column: Yes or No. |
| TrimTrailingBlanks | varchar(35) | Trim the trailing blanks (yes or no). |
| FixedLenNullInSource | varchar(35) | For backward compatibility only. |
| 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 |
| Column name | Data type | Description |
|---|---|---|
| RowGuidCol | sysname | Name of the global unique identifier column |
| Column name | Data type | Description |
|---|---|---|
| Data_located_on_filegroup | nvarchar(128) | The file group in which the data is located (Primary, Secondary, or Transaction Log) |
| Column name | Data type | Description |
|---|---|---|
| index_name | sysname | Index name |
| Index_description | varchar(210) | Description of the index |
| index_keys | nvarchar(2078) | Column name(s) on which the index is built |
| Column name | Data type | Description |
|---|---|---|
| constraint_type | nvarchar(146) | Type of constraint. |
| constraint_name | nvarchar(128) | Name of the constraint. |
| 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. |
| Column name | Data type | Description |
|---|---|---|
| Table is referenced by | nvarchar(516) | Identifies other database objects that reference the table. |
If name is a system stored procedure or an extended stored procedure, sp_help returns this result set.
| 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 (total number of digits) |
| Scale | int | Number of digits to the right of the decimal point |
| Param_order | smallint | Order of the parameter |
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.
Execute permissions default to the public role.
This example lists information about each object in sysobjects.
USE master
EXEC sp_help sysobjects
This example displays information about the publishers table.
USE pubs
EXEC sp_help publishers
| sp_helpgroup | sp_helpuser |
| sp_helpindex | sp_helptrigger |
| sp_helprotect | System Stored Procedures |
| sp_helpserver |