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 |
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 |
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. |
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. |
Column name | Data type | Description |
Data_located_on_filegroup | nvarchar(128) | Filegroup 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 names 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. |
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. |
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. |