sp_help返回结果描述

sp_help 作为查看数据库中对象信息内置存储过程。

其执行结果，会根据其传入参数的不同而不同，具体如下：

一、sp_help语法：

sp_help [ [ @objname = ] 'name' ]

二、执行情况：

1、sp_help

即，不带任何参数执行sp_help的情况，会返回两个结果集，第一个结果集为当前数据库所有类型对象的概要信息。

第二个结果集为用户自定类型的信息集，具体结构如下：

结果集1：

Column name	Data type	Description
Name	nvarchar(128)	Object name
Owner	nvarchar(128)	Object owner (This is the database principal that owns object. Defaults to the owner of the schema that contains the object.)
Object_type	nvarchar(31)	Object type

结果集2：

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.

2、sp_help name(name为系统数据类型或者用户自定义数据类型时)

会返回一个结果集，结构如下：

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、sp_help name(name为系统表或者用户表时，If name is a system table, user table)

返回如下结果集：

结果集1（概要信息）

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

结果集2（列定义信息）

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. 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.

结果集3（标识列定义信息）

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

结果集4（Guid列定义信息）

Column name	Data type	Description
RowGuidCol	sysname	Name of the global unique identifier column.

结果集5（文件组信息）

Column name	Data type	Description
Data_located_on_filegroup	nvarchar(128)	Filegroup in which the data is located: Primary, Secondary, or Transaction Log.

结果集6（索引列信息）

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.

结果集7（列约束定义信息）

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. SET_NULL and SET_DEFAULT show as No action. Only applicable to FOREIGN KEY constraints.
status_enabled	varchar(8)	Indicates whether the constraint is enabled: Enabled, Disabled, or N/A. SET_NULL and SET_DEFAULT show as No action. 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.

结果集8（引用对象信息，这个返回结果不确定，可能有的表会有，有的表没有）

Column name	Data type	Description
Table is referenced by	nvarchar(516)	Identifies other database objects that reference the table.

4、sp_help name(name 为视图)

返回结果集的情况，与“name为系统表或者用户表”时返回结果集的前4个结果集相同。

即：

结果集1（概要信息结果集），结果集2（列定义信息结果集），结果集3（标识列定义信息结果集），结果集4（Guid列定义信息结果集）

5、sp_help name(name 为存储过程，函数，扩展存储过程时,tored procedures, functions, or extended stored procedures.)

会返回两个结果集。

结果集1（概要信息结果集）

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

结果集2（参数信息结果集）

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.