COLUMNPROPERTY (Transact-SQL)COLUMNPROPERTY (Transact-SQL)

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

此函式會傳回資料行或參數資訊。This function returns column or parameter information.

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

語法Syntax

COLUMNPROPERTY ( id , column , property )   

引數Arguments

idid
包含資料表或程序之識別碼 (ID) 的運算式An expression containing the identifier (ID) of the table or procedure.

columncolumn
包含資料行或參數名稱的運算式。An expression containing the name of the column or parameter.

propertyproperty
針對 id 引數,property 引數會指定 COLUMNPROPERTY 函式所傳回的資訊類型。For the id argument, the property argument specifies the information type that the COLUMNPROPERTY function will return. property 引數可以具有下列任何一個值:The property argument can have any one of these values:

ReplTest1Value DescriptionDescription 傳回的值Value returned
AllowsNullAllowsNull 允許 Null 值Allows null values. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
ColumnIdColumnId 對應於 sys.columns.column_id 的資料行識別碼值。Column ID value corresponding to sys.columns.column_id. 資料行識別碼Column ID

注意: 當查詢多個資料行時,資料行識別碼值順序可能會有間隙。Note: When querying multiple columns, gaps may appear in the sequence of Column ID values.
FullTextTypeColumnFullTextTypeColumn 資料表中保存 column 之文件類型資訊的 TYPE COLUMN。The TYPE COLUMN in the table holding the document type information of the column. 此函式的第二個參數所傳遞之資料行名稱運算式的全文檢索 TYPE COLUMN 識別碼。ID of the full-text TYPE COLUMN for the column name expression passed as the second parameter of this function.
GeneratedAlwaysTypeGeneratedAlwaysType 是系統產生的資料行值。Is column value system-generated. 對應到 sys.columns.generated_always_typeCorresponds to sys.columns.generated_always_type 適用於SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017.

0:一律不會產生0: Not generated always

1:一律在資料列開頭產生1: Generated always at row start

2:一律在資料列結尾產生2: Generated always at row end
IsColumnSetIsColumnSet 資料行是資料行集。Column is a column set. 如需詳細資訊,請參閱 使用資料行集For more information, see Use Column Sets. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
IsComputedIsComputed 資料行是一個計算資料行。Column is a computed column. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
IsCursorTypeIsCursorType 程序參數的類型是 CURSOR。Procedure parameter is of type CURSOR. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
IsDeterministicIsDeterministic 資料行具有決定性。Column is deterministic. 這個屬性只適用於計算資料行和檢視資料行。This property applies only to computed columns and view columns. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input. 不是計算資料行或檢視資料行。Not a computed column or view column.
IsFulltextIndexedIsFulltextIndexed 資料行已完成全文檢索索引的登錄。Column is registered for full-text indexing. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
IsHiddenIsHidden 是系統產生的資料行值。Is column value system-generated. 對應至 sys.columns.is_hiddenCorresponds to sys.columns.is_hidden 適用於SQL Server 2017SQL Server 2017SQL Server 2017SQL Server 2017Applies to: SQL Server 2017SQL Server 2017 through SQL Server 2017SQL Server 2017.

0:不隱藏0: Not hidden

1:Hidden1: Hidden
IsIdentityIsIdentity 資料行使用 IDENTITY 屬性。Column uses the IDENTITY property. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
IsIdNotForReplIsIdNotForRepl 資料行會檢查 IDENTITY_INSERT 設定。Column checks for the IDENTITY_INSERT setting. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
IsIndexableIsIndexable 資料行可以建立索引。Column can be indexed. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
IsOutParamIsOutParam 程序參數是一個輸出參數。Procedure parameter is an output parameter. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
IsPreciseIsPrecise 資料行是精確的。Column is precise. 這個屬性只適用於具決定性的資料行。This property applies only to deterministic columns. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input. 不是具決定性的資料行Not a deterministic column
IsRowGuidColIsRowGuidCol 資料行具有 uniqueidentifier 資料類型,並且使用 ROWGUIDCOL 屬性所定義。Column has the uniqueidentifier data type, and is defined with the ROWGUIDCOL property. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
IsSparseIsSparse 資料行是疏鬆資料行。Column is a sparse column. 如需詳細資訊,請參閱 使用疏鬆資料行For more information, see Use Sparse Columns. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
IsSystemVerifiedIsSystemVerified Database EngineDatabase Engine 可以驗證資料行的決定性和有效位數屬性。The Database EngineDatabase Engine can verify the determinism and precision properties of the column. 這個屬性只適用於計算資料行和檢視資料行。This property applies only to computed columns and columns of views. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
IsXmlIndexableIsXmlIndexable XML 資料行可用於 XML 索引中。The XML column can be used in an XML index. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
有效位數Precision 資料行或參數的資料類型長度。Data type length of the column or parameter. 指定的資料行資料類型長度The length of the specified column data type

-1:xml 或大數值類型-1: xml or large value types

NULL:無效的輸入。NULL: invalid input.
小數位數Scale 資料行或參數資料類型的小數位數。Scale for the column or parameter data type. 小數位數值The scale value

NULL:無效的輸入。NULL: invalid input.
StatisticalSemanticsStatisticalSemantics 資料行啟用語意索引。Column is enabled for semantic indexing. 1:TRUE1: TRUE

0:FALSE0: FALSE
SystemDataAccessSystemDataAccess 資料行是從存取 SQL ServerSQL Server 系統目錄或虛擬系統資料表中之資料的函數衍生而來。Column is derived from a function that accesses data in the system catalogs or virtual system tables of SQL ServerSQL Server. 這個屬性只適用於計算資料行和檢視資料行。This property applies only to computed columns and columns of views. 1:TRUE (表示唯讀存取。)1: TRUE (Indicates read-only access.)

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
UserDataAccessUserDataAccess 資料行是從存取 SQL ServerSQL Server 本機執行個體所儲存的使用者資料表 (包括檢視和暫存資料表) 中之資料的函數衍生而來。Column is derived from a function that accesses data in user tables, including views and temporary tables, stored in the local instance of SQL ServerSQL Server. 這個屬性只適用於計算資料行和檢視資料行。This property applies only to computed columns and columns of views. 1:TRUE (表示唯讀存取。)1: TRUE (Indicates read-only access.)

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.
UsesAnsiTrimUsesAnsiTrim ANSI_PADDING 已在資料表建立時設定為 ON。ANSI_PADDING was set ON at time of table creation. 這個屬性只適用對象 charvarchar 類型的資料行或參數。This property applies only to columns or parameters of type char or varchar. 1:TRUE1: TRUE

0:FALSE0: FALSE

NULL:無效的輸入。NULL: invalid input.

傳回類型Return types

intint

例外狀況Exceptions

發生錯誤或呼叫端沒有檢視物件的權限時,會傳回 NULL。Returns NULL on error, or if a caller does not have permission to view the object.

使用者只能檢視使用者擁有或被授與某些權限之安全性實體的中繼資料。A user can only view the metadata of securables that the user owns or on which the user has been granted permission. 這表示,如果使用者沒有物件的正確權限,則發出中繼資料的內建函式 (例如 COLUMNPROPERTY) 可能會傳回 NULL。This means that metadata-emitting, built-in functions such as COLUMNPROPERTY might return NULL, if the user does not have correct permission on the object. 如需詳細資訊,請參閱中繼資料可見性設定See Metadata Visibility Configuration for more information.

RemarksRemarks

檢查資料行的決定性屬性時,請先測試資料行是否為計算資料行。When checking the deterministic property of a column, first test whether the column is a computed column. 如果是非計算資料行,IsDeterministic 引數會傳回 NULL。The IsDeterministic argument returns NULL for noncomputed columns. 您可以將計算資料行指定成索引資料行。Computed columns can be specified as index columns.

範例Examples

此範例會傳回 LastName 資料行的長度。This example returns the length of the LastName column.

USE AdventureWorks2012;  
GO  
SELECT COLUMNPROPERTY( OBJECT_ID('Person.Person'),'LastName','PRECISION')AS 'Column Length';  
GO  

以下為結果集:Here is the result set.

Column Length
-------------
50

另請參閱See also

中繼資料函式 (Transact-SQL)Metadata Functions (Transact-SQL)
TYPEPROPERTY (Transact-SQL)TYPEPROPERTY (Transact-SQL)