COLUMNPROPERTY (Transact-SQL)

**APPLIES TO:** ![yes](media/yes.png)SQL Server (starting with 2008) ![yes](media/yes.png)Azure SQL Database ![no](media/no.png)Azure SQL Data Warehouse ![no](media/no.png)Parallel Data Warehouse

This function returns column or parameter information.

Topic link icon Transact-SQL Syntax Conventions

Syntax

COLUMNPROPERTY ( id , column , property )

Arguments

id
An expression containing the identifier (ID) of the table or procedure.

column
An expression containing the name of the column or parameter.

property
For the id argument, the property argument specifies the information type that the COLUMNPROPERTY function will return. The property argument can have any one of these values:

Value Description Value returned
AllowsNull Allows null values. 1: TRUE

0: FALSE

NULL: invalid input.
ColumnId 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.
FullTextTypeColumn The TYPE COLUMN in the table holding the document type information of the column. ID of the full-text TYPE COLUMN for the column name expression passed as the second parameter of this function.
GeneratedAlwaysType Is column value system-generated. Corresponds to sys.columns.generated_always_type Applies to: [!INCLUDEssSQL15] through [!INCLUDEssCurrent].

0: Not generated always

1: Generated always at row start

2: Generated always at row end
IsColumnSet Column is a column set. For more information, see Use Column Sets. 1: TRUE

0: FALSE

NULL: invalid input.
IsComputed Column is a computed column. 1: TRUE

0: FALSE

NULL: invalid input.
IsCursorType Procedure parameter is of type CURSOR. 1: TRUE

0: FALSE

NULL: invalid input.
IsDeterministic Column is deterministic. This property applies only to computed columns and view columns. 1: TRUE

0: FALSE

NULL: invalid input. Not a computed column or view column.
IsFulltextIndexed Column is registered for full-text indexing. 1: TRUE

0: FALSE

NULL: invalid input.
IsHidden Is column value sysem-generated. Corresponds to sys.columns.is_hidden Applies to: [!INCLUDEssCurrentLong] through [!INCLUDEssCurrent].

0: Not hidden

1: Hidden
IsIdentity Column uses the IDENTITY property. 1: TRUE

0: FALSE

NULL: invalid input.
IsIdNotForRepl Column checks for the IDENTITY_INSERT setting. 1: TRUE

0: FALSE

NULL: invalid input.
IsIndexable Column can be indexed. 1: TRUE

0: FALSE

NULL: invalid input.
IsOutParam Procedure parameter is an output parameter. 1: TRUE

0: FALSE

NULL: invalid input.
IsPrecise Column is precise. This property applies only to deterministic columns. 1: TRUE

0: FALSE

NULL: invalid input. Not a deterministic column
IsRowGuidCol Column has the uniqueidentifier data type, and is defined with the ROWGUIDCOL property. 1: TRUE

0: FALSE

NULL: invalid input.
IsSparse Column is a sparse column. For more information, see Use Sparse Columns. 1: TRUE

0: FALSE

NULL: invalid input.
IsSystemVerified The [!INCLUDEssDE] can verify the determinism and precision properties of the column. This property applies only to computed columns and columns of views. 1: TRUE

0: FALSE

NULL: invalid input.
IsXmlIndexable The XML column can be used in an XML index. 1: TRUE

0: FALSE

NULL: invalid input.
Precision Data type length of the column or parameter. The length of the specified column data type

-1: xml or large value types

NULL: invalid input.
Scale Scale for the column or parameter data type. The scale value

NULL: invalid input.
StatisticalSemantics Column is enabled for semantic indexing. 1: TRUE

0: FALSE
SystemDataAccess Column is derived from a function that accesses data in the system catalogs or virtual system tables of [!INCLUDEssNoVersion]. This property applies only to computed columns and columns of views. 1: TRUE (Indicates read-only access.)

0: FALSE

NULL: invalid input.
UserDataAccess Column is derived from a function that accesses data in user tables, including views and temporary tables, stored in the local instance of [!INCLUDEssNoVersion]. This property applies only to computed columns and columns of views. 1: TRUE (Indicates read-only access.)

0: FALSE

NULL: invalid input.
UsesAnsiTrim ANSI_PADDING was set ON at time of table creation. This property applies only to columns or parameters of type char or varchar. 1: TRUE

0: FALSE

NULL: invalid input.

Return types

int

Exceptions

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

Remarks

When checking the deterministic property of a column, first test whether the column is a computed column. The IsDeterministic argument returns NULL for noncomputed columns. Computed columns can be specified as index columns.

Examples

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

Metadata Functions (Transact-SQL)
TYPEPROPERTY (Transact-SQL)