Transact-SQL Reference
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')