sp_help (Transact-SQL)sp_help (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

報告資料庫物件( sysobjects相容性檢視中所列的任何物件)、使用者定義資料類型或資料類型的相關資訊。Reports information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

  
sp_help [ [ @objname = ] 'name' ]  

引數Arguments

[ @objname = ] 'name' 是任何物件的名稱, sysobjectssystypes資料表中的任何使用者自訂資料類型。[ @objname = ] 'name' Is the name of any object, in sysobjects or any user-defined data type in the systypes table. nameNvarchar ( 776 ,預設值是 Null。name is nvarchar( 776 ), with a default of NULL. 不接受資料庫名稱。Database names are not acceptable. 兩個或三個部分的名稱必須加以分隔,例如 'Person.AddressType' 或 [Person.AddressType]。Two or three part names must be delimited, such as 'Person.AddressType' or [Person.AddressType].

傳回碼值Return Code Values

0 (成功) 或 1 (失敗)0 (success) or 1 (failure)

結果集Result Sets

傳回的結果集會根據名稱是否已指定、指定時間,以及它是哪個資料庫物件而定。The result sets that are returned depend on whether name is specified, when it is specified, and which database object it is.

  1. 如果執行sp_help不含任何引數,則會傳回目前資料庫中所有類型之物件的摘要資訊。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 )nvarchar( 128 ) 物件名稱Object name
    擁有者Owner nvarchar( 128 )nvarchar( 128 ) 物件擁有者 (這是擁有物件的資料庫主體,Object owner (This is the database principal that owns object. 預設為包含物件之結構描述的擁有者)。Defaults to the owner of the schema that contains the object.)
    Object_typeObject_type Nvarchar ( 31 nvarchar( 31 ) 物件類型Object type
  2. 如果nameSQL ServerSQL Server 資料類型或使用者自訂資料類型, sp_help會傳回這個結果集。If name is a SQL ServerSQL Server data type or user-defined data type, sp_help returns this result set.

    資料行名稱Column name [名稱]Data type 描述Description
    Type_nameType_name nvarchar( 128 )nvarchar( 128 ) 資料類型名稱。Data type name.
    Storage_typeStorage_type nvarchar( 128 )nvarchar( 128 ) SQL ServerSQL Server 類型名稱。type name.
    長度Length smallintsmallint 資料類型的實際長度 (以位元組為單位)。Physical length of the data type (in bytes).
    PrecPrec intint 有效位數 (總位數)。Precision (total number of digits).
    小數位數Scale intint 小數點右側的位數。Number of digits to the right of the decimal.
    可為 NullNullable Varchar ( 35 varchar( 35 ) 指出是否允許 NULL 值:[是] 或 [否]。Indicates whether NULL values are allowed: Yes or No.
    Default_nameDefault_name nvarchar( 128 )nvarchar( 128 ) 與這個類型繫結的預設值名稱。Name of a default bound to this type.

    NULL = 未繫結預設值。NULL = No default is bound.
    Rule_nameRule_name nvarchar( 128 )nvarchar( 128 ) 與這個類型繫結的規則名稱。Name of a rule bound to this type.

    NULL = 未繫結預設值。NULL = No default is bound.
    定序Collation sysnamesysname 資料類型的定序。Collation of the data type. 非字元資料類型是 NULL。NULL for non-character data types.
  3. 如果name是資料類型以外的任何資料庫物件, sp_help會根據指定的物件類型,傳回此結果集和其他結果集。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 )nvarchar( 128 ) 資料表名稱Table name
    擁有者Owner nvarchar( 128 )nvarchar( 128 ) 資料表擁有者Table owner
    類型Type Nvarchar ( 31 nvarchar( 31 ) 資料表類型Table type
    Created_datetimeCreated_datetime datetimedatetime 資料表的建立日期Date table created

    視指定的資料庫物件而定, sp_help會傳回額外的結果集。Depending on the database object specified, sp_help returns additional result sets.

    如果name是系統資料表、使用者資料表或 view, sp_help會傳回下列結果集。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_nameColumn_name nvarchar( 128 )nvarchar( 128 ) 資料行名稱。Column name.
      類型Type nvarchar( 128 )nvarchar( 128 ) 資料行資料類型。Column data type.
      Computed Varchar ( 35 varchar( 35 ) 指出是否計算資料行中的值:[是] 或 [否]。Indicates whether the values in the column are computed: Yes or No.
      長度Length intint 資料行長度 (以位元組為單位)。Column length in bytes.

      注意:如果資料類型是大數數值型別(Varchar (max)Nvarchar (max)Varbinary (max)xml),此值會顯示為-1。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.
      PrecPrec char ( 5 char( 5 ) 資料行有效位數。Column precision.
      小數位數Scale char ( 5 char( 5 ) 資料行小數位數。Column scale.
      可為 NullNullable Varchar ( 35 varchar( 35 ) 指出資料行是否允許 NULL 值:[是] 或 [否]。Indicates whether NULL values are allowed in the column: Yes or No.
      TrimTrailingBlanksTrimTrailingBlanks Varchar ( 35 varchar( 35 ) 修剪尾端空白。Trim the trailing blanks. 傳回 [是] 或 [否]。Returns Yes or No.
      FixedLenNullInSourceFixedLenNullInSource Varchar ( 35 varchar( 35 ) 只是為了與舊版相容。For backward compatibility only.
      定序Collation sysnamesysname 資料行的定序。Collation of the column. 非字元資料類型是 NULL。NULL for noncharacter data types.
    • 在識別欄位上傳回的其他結果集:Additional result set returned on identity columns:

      資料行名稱Column name [名稱]Data type 描述Description
      識別Identity nvarchar( 128 )nvarchar( 128 ) 資料類型宣告為識別的資料行名稱。Column name whose data type is declared as identity.
      種子Seed numericnumeric 識別欄位的起始值。Starting value for the identity column.
      [遞增]Increment numericnumeric 這個資料行的值所用的遞增。Increment to use for values in this column.
      不可複寫Not For Replication intint 當複寫登入(例如sqlrepl)將資料插入資料表時,不會強制執行 IDENTITY 屬性:IDENTITY property is not enforced when a replication login, such as sqlrepl, inserts data into the table:

      1 = True1 = True

      0 = False0 = False
    • 在資料行上傳回的其他結果集:Additional result set returned on columns:

      資料行名稱Column name [名稱]Data type 描述Description
      RowGuidColRowGuidCol sysnamesysname 全域唯一識別碼資料行的名稱。Name of the global unique identifier column.
    • 在檔案群組上傳回的其他結果集:Additional result set returned on filegroups:

      資料行名稱Column name [名稱]Data type 描述Description
      Data_located_on_filegroupData_located_on_filegroup nvarchar( 128 )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_nameindex_name sysnamesysname 索引名稱。Index name.
      Index_descriptionIndex_description Varchar ( 210 varchar( 210 ) 索引的描述。Description of the index.
      index_keysindex_keys Nvarchar ( 2078 nvarchar( 2078 ) 建立索引的資料行名稱。Column names on which the index is built. 如果是 xVelocity 記憶體最佳化的資料行存放區索引,則傳回 NULL。Returns NULL for xVelocity memory optimized columnstore indexes.
    • 在條件約束上傳回的其他結果集:Additional result set returned on constraints:

      資料行名稱Column name [名稱]Data type 描述Description
      constraint_typeconstraint_type Nvarchar ( 146 nvarchar( 146 ) 條件約束的類型。Type of constraint.
      constraint_nameconstraint_name nvarchar( 128 )nvarchar( 128 ) 條件約束的名稱。Name of the constraint.
      delete_actiondelete_action Nvarchar ( 9 nvarchar( 9 ) 指出 DELETE 動作是:NO_ACTION、CASCADE、SET_NULL、SET_DEFAULT 或 N/A。Indicates whether the DELETE action is: NO_ACTION, CASCADE, SET_NULL, SET_DEFAULT, or N/A.

      只適用於 FOREIGN KEY 條件約束。Only applicable to FOREIGN KEY constraints.
      update_actionupdate_action Nvarchar ( 9 nvarchar( 9 ) 指出 UPDATE 動作是:NO_ACTION、CASCADE、SET_NULL、SET_DEFAULT 或 N/A。Indicates whether the UPDATE action is: NO_ACTION, CASCADE, SET_NULL, SET_DEFAULT, or N/A.

      只適用於 FOREIGN KEY 條件約束。Only applicable to FOREIGN KEY constraints.
      status_enabledstatus_enabled Varchar ( 8 varchar( 8 ) 指出是否啟用條件約束:已啟用、已停用或 N/A。Indicates whether the constraint is enabled: Enabled, Disabled, or N/A.

      只適用於 CHECK 和 FOREIGN KEY 條件約束。Only applicable to CHECK and FOREIGN KEY constraints.
      status_for_replicationstatus_for_replication Varchar ( 19 varchar( 19 ) 指出條件約束是否針對複寫。Indicates whether the constraint is for replication.

      只適用於 CHECK 和 FOREIGN KEY 條件約束。Only applicable to CHECK and FOREIGN KEY constraints.
      constraint_keysconstraint_keys Nvarchar ( 2078 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 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_nameParameter_name nvarchar( 128 )nvarchar( 128 ) 預存程序參數名稱。Stored procedure parameter name.
      類型Type nvarchar( 128 )nvarchar( 128 ) 預存程序參數的資料類型。Data type of the stored procedure parameter.
      長度Length smallintsmallint 最大的實體儲存體長度 (以位元組為單位)。Maximum physical storage length, in bytes.
      PrecPrec intint 有效位數或總位數。Precision or total number of digits.
      小數位數Scale intint 小數點右側的位數。Number of digits to the right of the decimal point.
      Param_orderParam_order smallintsmallint 參數的順序。Order of the parameter.

RemarksRemarks

Sp_help程式只會在目前資料庫中尋找物件。The sp_help procedure looks for an object in the current database only.

如果未指定namesp_help會列出目前資料庫中所有物件的物件名稱、擁有者和物件類型。When name is not specified, sp_help lists object names, owners, and object types for all objects in the current database. sp_helptrigger提供觸發程式的相關資訊。sp_helptrigger provides information about triggers.

sp_help只會公開能排序的索引資料行;因此,它不會公開 XML 索引或空間索引的相關資訊。sp_help exposes only orderable index columns; therefore, it does not expose information about XML indexes or spatial indexes.

PermissionsPermissions

需要 public 角色中的成員資格。Requires membership in the public role. 使用者在objname上必須至少有一個許可權。The user must have at least one permission on objname. 若要檢視資料行條件約束索引鍵、預設值或規則,您必須具有此資料表的 VIEW DEFINITION 權限。To view column constraint keys, defaults, or rules, you must have VIEW DEFINITION permission on the table.

範例Examples

A.A. 傳回所有物件的相關資訊Returning information about all objects

下列範例會列出 master 資料庫中每個物件的相關資訊。The following example lists information about each object in the master database.

USE master;  
GO  
EXEC sp_help;  
GO  

b.B. 傳回單一物件的相關資訊Returning information about a single object

下列範例會顯示 Person 資料表的相關資訊。The following example displays information about the Person table.

USE AdventureWorks2012;  
GO  
EXEC sp_help 'Person.Person';  
GO  

另請參閱See Also

資料庫引擎預存(程式 transact-sql) Database Engine Stored Procedures (Transact-SQL)
sp_helpindex (transact-sql) sp_helpindex (Transact-SQL)
sp_helprotect (Transact-SQL) sp_helprotect (Transact-SQL)
sp_helpserver (Transact-SQL) sp_helpserver (Transact-SQL)
sp_helptrigger (Transact-SQL) sp_helptrigger (Transact-SQL)
sp_helpuser (transact-sql) sp_helpuser (Transact-SQL)
系統預存程序 (Transact-SQL) System Stored Procedures (Transact-SQL)
sysobjects (transact-sql)sys.sysobjects (Transact-SQL)