COLUMNPROPERTY (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This function returns column or parameter information.
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: SQL Server 2016 (13.x) and later. 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 system-generated. Corresponds to sys.columns.is_hidden | Applies to: SQL Server 2016 (13.x) and later. 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 Database Engine 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 SQL Server. 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 SQL Server. 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 AdventureWorks2022;
GO
SELECT COLUMNPROPERTY( OBJECT_ID('Person.Person'),'LastName','PRECISION')AS 'Column Length';
GO
Here's the result set.
Column Length
-------------
50
See also
Metadata Functions (Transact-SQL)
TYPEPROPERTY (Transact-SQL)