--- description: "sys.columns (Transact-SQL)" title: "sys.columns (Transact-SQL)" ms.custom: "" ms.date: "05/25/2021" ms.prod: sql ms.prod_service: "database-engine, sql-database, synapse-analytics, pdw" ms.reviewer: "" ms.technology: system-objects ms.topic: "reference" f1_keywords: - "sys.columns_TSQL" - "sys.columns" - "columns_TSQL" - "columns" dev_langs: - "TSQL" helpviewer_keywords: - "sys.columns catalog view" author: WilliamDAssafMSFT ms.author: wiassaf monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current" --- # sys.columns (Transact-SQL) [!INCLUDE [sql-asdb-asdbmi-asa-pdw](../../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw.md)] Returns a row for each column of an object that has columns, such as views or tables. The following is a list of object types that have columns: - Table-valued assembly functions (FT) - Inline table-valued SQL functions (IF) - Internal tables (IT) - System tables (S) - Table-valued SQL functions (TF) - User tables (U) - Views (V) |Column name|Data type|Description| |-----------------|---------------|-----------------| |object_id|**int**|ID of the object to which this column belongs.| |name|**sysname**|Name of the column. Is unique within the object.| |column_id|**int**|ID of the column. Is unique within the object.

Column IDs might not be sequential.| |system_type_id|**tinyint**|ID of the system type of the column.| |user_type_id|**int**|ID of the type of the column as defined by the user.

To return the name of the type, join to the [sys.types](../../relational-databases/system-catalog-views/sys-types-transact-sql.md) catalog view on this column.| |max_length|**smallint**|Maximum length (in bytes) of the column.

-1 = Column data type is **varchar(max)**, **nvarchar(max)**, **varbinary(max)**, or **xml**.

For **text** columns, the max_length value will be 16 or the value set by sp_tableoption 'text in row'.| |precision|**tinyint**|Precision of the column if numeric-based; otherwise, 0.| |scale|**tinyint**|Scale of column if numeric-based; otherwise, 0.| |collation_name|**sysname**|Name of the collation of the column if character-based; otherwise `NULL`.| |is_nullable|**bit**|1 = Column is nullable.| |is_ansi_padded|**bit**|1 = Column uses ANSI_PADDING ON behavior if character, binary, or variant.

0 = Column is not character, binary, or variant.| |is_rowguidcol|**bit**|1 = Column is a declared ROWGUIDCOL.| |is_identity|**bit**|1 = Column has identity values| |is_computed|**bit**|1 = Column is a computed column.| |is_filestream|**bit**|1 = Column is a FILESTREAM column.| |is_replicated|**bit**|1 = Column is replicated.| |is_non_sql_subscribed|**bit**|1 = Column has a non-SQL Server subscriber.| |is_merge_published|**bit**|1 = Column is merge-published.| |is_dts_replicated|**bit**|1 = Column is replicated by using [!INCLUDE[ssIS](../../includes/ssis-md.md)].| |is_xml_document|**bit**|1 = Content is a complete XML document.

0 = Content is a document fragment or the column data type is not **xml**.| |xml_collection_id|**int**|Nonzero if the data type of the column is **xml** and the XML is typed. The value will be the ID of the collection containing the validating XML schema namespace of the column.

0 = No XML schema collection.| |default_object_id|**int**|ID of the default object, regardless of whether it is a stand-alone object [sys.sp_bindefault](../../relational-databases/system-stored-procedures/sp-bindefault-transact-sql.md), or an inline, column-level DEFAULT constraint. The parent_object_id column of an inline column-level default object is a reference back to the table itself.

0 = No default.| |rule_object_id|**int**|ID of the stand-alone rule bound to the column by using sys.sp_bindrule.

0 = No stand-alone rule. For column-level CHECK constraints, see [sys.check_constraints (Transact-SQL)](../../relational-databases/system-catalog-views/sys-check-constraints-transact-sql.md).| |is_sparse|**bit**|1 = Column is a sparse column. For more information, see [Use Sparse Columns](../../relational-databases/tables/use-sparse-columns.md).| |is_column_set|**bit**|1 = Column is a column set. For more information, see [Use Sparse Columns](../../relational-databases/tables/use-sparse-columns.md).| |generated_always_type|**tinyint**|**Applies to**: [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] and later, [!INCLUDE[ssSDS_md](../../includes/sssds-md.md)].

Identifies when the column value is generated (will always be 0 for columns in system tables):

0 = NOT_APPLICABLE
1 = AS_ROW_START
2 = AS_ROW_END
7 = AS_TRANSACTION_ID_START
8 = AS_TRANSACTION_ID_END
9 = AS_SEQUENCE_NUMBER_START
10 = AS_SEQUENCE_NUMBER_END

For more information, see [Temporal Tables (Relational databases)](../../relational-databases/tables/temporal-tables.md).| |generated_always_type_desc|**nvarchar(60)**|**Applies to**: [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] and later, [!INCLUDE[ssSDS_md](../../includes/sssds-md.md)].

Textual description of `generated_always_type`'s value (always NOT_APPLICABLE for columns in system tables)

NOT_APPLICABLE
AS_ROW_START
AS_ROW_END
AS_TRANSACTION_ID_START
AS_TRANSACTION_ID_END
AS_SEQUENCE_NUMBER_START
AS_SEQUENCE_NUMBER_END| |encryption_type|**int**|**Applies to**: [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] and later, [!INCLUDE[ssSDS_md](../../includes/sssds-md.md)].

Encryption type:

1 = Deterministic encryption

2 = Randomized encryption| |encryption_type_desc|**nvarchar(64)**|**Applies to**: [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] and later, [!INCLUDE[ssSDS_md](../../includes/sssds-md.md)].

Encryption type description:

RANDOMIZED

DETERMINISTIC| |encryption_algorithm_name|**sysname**|**Applies to**: [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] and later, [!INCLUDE[ssSDS_md](../../includes/sssds-md.md)].

Name of encryption algorithm.

Only AEAD_AES_256_CBC_HMAC_SHA_512 is supported.| |column_encryption_key_id|**int**|**Applies to**: [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] and later, [!INCLUDE[ssSDS_md](../../includes/sssds-md.md)].

ID of the CEK.| |column_encryption_key_database_name|**sysname**|**Applies to**: [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] and later, [!INCLUDE[ssSDW_md](../../includes/sssds-md.md)].

The name of the database where the column encryption key exists if different than the database of the column. `NULL` if the key exists in the same database as the column.| |is_hidden|**bit**|**Applies to**: [!INCLUDE[ssCurrentLong](../../includes/sscurrent-md.md)] and later, [!INCLUDE[ssSDS_md](../../includes/sssds-md.md)].

Indicates if the column is hidden:

0 = regular, not-hidden, visible column

1 = hidden column| |is_masked|**bit**|**Applies to**: [!INCLUDE[ssCurrentLong](../../includes/sscurrent-md.md)] and later, [!INCLUDE[ssSDS_md](../../includes/sssds-md.md)].

Indicates if the column is masked by a dynamic data masking:

0 = regular, not-masked column

1 = column is masked| |graph_type |**int** |Internal column with a set of values. The values are between 1-8 for graph columns and `NULL` for others. | |graph_type_desc |**nvarchar(60)** |internal column with a set of values | |ledger_view_column_type|**tinyint**|**Applies to**: [!INCLUDE[ssSDS_md](../../includes/sssds-md.md)].

If not NULL, indicates the type of a column in a ledger view:

1 = TRANSACTION_ID
2 = SEQUENCE_NUMBER
3 = OPERATION_TYPE
4 = OPERATION_TYPE_DESC| |ledger_view_column_type_desc|**nvarchar(60)**|**Applies to**: [!INCLUDE[ssSDS_md](../../includes/sssds-md.md)].

If not NULL, contains a textual description of the the type of a column in a ledger view:

TRANSACTION_ID
SEQUENCE_NUMBER
OPERATION_TYPE
OPERATION_TYPE_DESC| ## Permissions [!INCLUDE[ssCatViewPerm](../../includes/sscatviewperm-md.md)] For more information, see [Metadata Visibility Configuration](../../relational-databases/security/metadata-visibility-configuration.md). ## See Also [System Views (Transact-SQL)](../../t-sql/language-reference.md) [Object Catalog Views (Transact-SQL)](../../relational-databases/system-catalog-views/object-catalog-views-transact-sql.md) [Catalog Views (Transact-SQL)](../../relational-databases/system-catalog-views/catalog-views-transact-sql.md) [Querying the SQL Server System Catalog FAQ](../../relational-databases/system-catalog-views/querying-the-sql-server-system-catalog-faq.yml) [sys.all_columns (Transact-SQL)](../../relational-databases/system-catalog-views/sys-all-columns-transact-sql.md) [sys.system_columns (Transact-SQL)](../../relational-databases/system-catalog-views/sys-system-columns-transact-sql.md)