Use of sp_help System Stored Procedure

In this blog i am going to explain the use of sp_help system stored procedure.It can be very useful for database developer.

sp_help

It gives information about various database objects i.e. any objects comes under sysobjects table,a user defined data type or a data type supplied by SQL server. It is executable for all public roles.It gives information of the current database only.

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. The argument is optional.

Result Sets

1)Without giving any object name as parameters:It gives summary information of objects of all types that exist in the current database.

Exec sp_Help

Result : It gives columns of Name for Object Name,Owner for object owner and type for object

Column name	Data type	Description
Name	nvarchar(128)	Object name
Owner	nvarchar(128)	Object owner
Object_type	nvarchar(31)	Object type

2)Give table name as an object name:

Exec sp_help tablename

Result: It gives datasets containing various information like table name.Owner,Created date time,object type, Column names,Column length,Column Data type,Nullable or not,Collations,Identity,Keys ,Constraint name,Constraint type,Constraint keys,Foreign key related information etc.

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 object 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 object name is a system stored procedure or an extended stored procedure, sp_help returns this result set.

Exec sp_help spname

Result :It gives datasets containing sp name sp type ,sp owner,sp's Created datetime,Parameter names,Parameter types,Parameter length,Collations etc.

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