COLUMNPROPERTY

Returns information about a column or procedure parameter.

Syntax

COLUMNPROPERTY ( id , column , property )

Arguments

id

Is an expression containing the identifier (ID) of the table or procedure.

column

Is an expression containing the name of the column or parameter.

property

Is an expression containing the information to be returned for id, and can be any of these values.

Value	Description	Value returned
AllowsNull	Allows null values.	1 = TRUE 0 = FALSE NULL = Invalid input
IsComputed	The column is a computed column.	1 = TRUE 0 = FALSE NULL = Invalid input
IsCursorType	The procedure parameter is of type CURSOR.	1 = TRUE 0 = FALSE NULL = Invalid input
IsDeterministic	The 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	The column has been registered for full-text indexing.	1 = TRUE 0 = FALSE NULL = Invalid input
IsIdentity	The column uses the IDENTITY property.	1 = TRUE 0 = FALSE NULL = Invalid input
IsIdNotForRepl	The column checks for the IDENTITY_INSERT setting. If IDENTITY NOT FOR REPLICATION is specified, the IDENTITY_INSERT setting is not checked.	1 = TRUE 0 = FALSE NULL = Invalid input
IsIndexable	The column can be indexed.	1 = TRUE 0 = FALSE NULL = Invalid input
IsOutParam	The procedure parameter is an output parameter.	1 = TRUE 0 = FALSE NULL = Invalid input
IsPrecise	The column is precise. This property applies only to deterministic columns.	1 = TRUE 0 = FALSE NULL = Invalid input. Not a deterministic column
IsRowGuidCol	The column has the uniqueidentifier data type and is defined with the ROWGUIDCOL property.	1 = TRUE 0 = FALSE NULL = Invalid input
Precision	Precision for the data type of the column or parameter.	The precision of the specified column data type NULL = Invalid input
Scale	Scale for the data type of the column or parameter.	The scale NULL = Invalid input
UsesAnsiTrim	ANSI padding setting was ON when the table was initially created.	1= TRUE 0= FALSE NULL = Invalid input

Return Types

int

Remarks

When checking a column's deterministic property, test first whether the column is a computed column. IsDeterministic returns NULL for noncomputed columns.

Computed columns can be specified as index columns.

Examples

This example returns the length of the au_lname column.

SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname','PRECISION')

See Also

Metadata Functions

OBJECTPROPERTY

TYPEPROPERTY