sp_help (Transact-SQL)
Reports information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type.
.gif "Topic link icon")
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.
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 = 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.
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
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.
.gif "Note")
Note
If the column data type is a large value type (varchar(max), nvarchar(max), varbinary(max), or xml), the value will display as -1.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. Returns Yes or No.
FixedLenNullInSource
varchar(35)
For backward compatibility only.
Collation
sysname
Collation of the column. NULL for noncharacter 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)
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.
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.
sp_help exposes only orderable index columns; therefore, it does not expose information about XML indexes or spatial indexes.
Permissions
Requires membership in the public role. The user must have at least one permission on objname. To view column constraint keys, defaults, or rules, you must have VIEW DEFINITION permission on the table.
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