sp_help
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™.
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 returned depend on whether name is specified, and when specified, what database object it is.
- 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. Collation sysname Collation of the data type. NULL for non-character data types.
- If name is any database object (other than a data 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).
- Additional result set returned on column objects:
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. Collation sysname Collation of the column. NULL for non-character data types.
- 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) The filegroup in which the data is located (Primary, Secondary, or Transaction Log).
- Additional result set returned on index:
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.
- 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.
- 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 column objects:
- 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.
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
Execute permissions default to the public role.
Examples
A. Return information about all objects
This example lists information about each object in sysobjects.
USE master
EXEC sp_help
B. Return information about a single object
This example displays information about the publishers table.
USE pubs
EXEC sp_help publishers