sys.columns (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Returns a row for each column of an object that has columns, such as views or tables. The following list contains the 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 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, ntext, and image columns, the max_length value is 16 (representing the 16-byte pointer only) 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 0 = Column isn't nullable
is_ansi_padded	bit	1 = Column uses ANSI_PADDING ON behavior if character, binary, or variant 0 = Column isn't 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 SSIS
is_xml_document	bit	1 = Content is a complete XML document 0 = Content is a document fragment, or the column data type isn't xml
xml_collection_id	int	Nonzero if the data type of the column is xml and the XML is typed. The value is 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's a stand-alone object sp_bindefault, 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.
is_sparse	bit	1 = Column is a sparse column. For more information, see Use sparse columns.
is_column_set	bit	1 = Column is a column set. For more information, see Use sparse columns.
generated_always_type	tinyint	Identifies when the column value is generated (is always 0 for columns in system tables). Applies to: SQL Server 2016 (13.x) and later versions, and SQL Database. 0 = NOT_APPLICABLE 1 = AS_ROW_START 2 = AS_ROW_END Applies to: SQL Server 2022 (16.x) and later versions, and SQL Database. 5 = AS_TRANSACTION_ID_START 6 = AS_TRANSACTION_ID_END 7 = AS_SEQUENCE_NUMBER_START 8 = AS_SEQUENCE_NUMBER_END For more information, see Temporal Tables (Relational databases).
generated_always_type_desc	nvarchar(60)	Textual description of the generated_always_type value (always NOT_APPLICABLE for columns in system tables) Applies to: SQL Server 2016 (13.x) and later versions, and SQL Database. NOT_APPLICABLE AS_ROW_START AS_ROW_END Applies to: SQL Server 2022 (16.x) and later versions, and SQL Database. AS_TRANSACTION_ID_START AS_TRANSACTION_ID_END AS_SEQUENCE_NUMBER_START AS_SEQUENCE_NUMBER_END
encryption_type	int	Encryption type: 1 = Deterministic encryption 2 = Randomized encryption Applies to: SQL Server 2016 (13.x) and later versions, and SQL Database
encryption_type_desc	nvarchar(64)	Encryption type description: RANDOMIZED DETERMINISTIC Applies to: SQL Server 2016 (13.x) and later versions, and SQL Database
encryption_algorithm_name	sysname	Name of encryption algorithm. Only AEAD_AES_256_CBC_HMAC_SHA_512 is supported. Applies to: SQL Server 2016 (13.x) and later versions, and SQL Database
column_encryption_key_id	int	ID of the column encryption key (CEK). Applies to: SQL Server 2016 (13.x) and later versions, and SQL Database
column_encryption_key_database_name	sysname	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. Applies to: SQL Server 2016 (13.x) and later versions, and SQL Database
is_hidden	bit	Indicates if the column is hidden: 0 = regular, not-hidden, visible column 1 = hidden column Applies to: SQL Server 2016 (13.x) and later versions, and SQL Database
is_masked	bit	Indicates if the column is masked by dynamic data masking: 0 = regular, not-masked column 1 = column is masked Applies to: SQL Server 2016 (13.x) and later versions, and SQL Database
graph_type	int	Internal column with a set of values. The values are between 1 and 8 for graph columns, and NULL for others.
graph_type_desc	nvarchar(60)	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
is_data_deletion_filter_column	bit	Indicates if the column is the data retention filter column for the table. Applies to: Azure SQL Edge
ledger_view_column_type	int	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 For more information, see Ledger overview. Applies to: SQL Server 2022 (16.x) and later versions, and SQL Database
ledger_view_column_type_desc	nvarchar(60)	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 Applies to: SQL Server 2022 (16.x) and later versions, and SQL Database
is_dropped_ledger_column	bit	Indicates a ledger table column that was dropped. Applies to: SQL Server 2022 (16.x) and later versions, and SQL Database

Permissions

The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration.

Usage examples

Get column details for a table

To get metadata for columns in a table you can use the following code:

CREATE TABLE dbo.[sample] (
    id INT NOT NULL
    ,col1 VARBINARY(10) NULL
    )
GO

SELECT c.[name] AS column_name
    ,t.[name] AS [type_name]
    ,c.[max_length]
    ,c.[precision]
    ,c.[scale]
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE object_id = object_id('dbo.sample');

Related content

• Object Catalog Views (Transact-SQL)
• System catalog views (Transact-SQL)
• Querying the SQL Server System Catalog FAQ
• sys.all_columns (Transact-SQL)
• sys.system_columns (Transact-SQL)
• sys.types (Transact-SQL)