Skip to content

sys-system-columns-transact-sql.md

Latest commit

 

History

History
78 lines (70 loc) · 8.3 KB

sys-system-columns-transact-sql.md

File metadata and controls

78 lines (70 loc) · 8.3 KB
title sys.system_columns (Transact-SQL)
description sys.system_columns (Transact-SQL)
author rwestMSFT
ms.author randolphwest
ms.date 11/28/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
ms.custom
ignite-2025
f1_keywords
system_columns_TSQL
system_columns
sys.system_columns
sys.system_columns_TSQL
helpviewer_keywords
sys.system_columns catalog view
dev_langs
TSQL
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb

sys.system_columns (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]

Contains a row for each column of system objects that have columns.

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 column.

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

For text columns, the max_length value is 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 the 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 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 declared to use filestream storage.
is_replicated bit 1 = Column is replicated.
is_non_sql_subscribed bit 1 = Column has a non-[!INCLUDE ssNoVersion] subscriber.
is_merge_published bit 1 = Column is merge-published.
is_dts_replicated bit 1 = Column is replicated by using [!INCLUDE 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 Non-zero if the column data type 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 sys.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. Or, 0 if there's 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 column sets.
generated_always_type tinyint Identifies when the column value is generated (always 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.

Applies to: [!INCLUDE sssql16-md] and later, [!INCLUDE ssSDS_md]. 7, 8, 9, 10 only applies to [!INCLUDE ssSDS_md].
generated_always_type_desc nvarchar(60) Applies to: [!INCLUDE sssql16-md] and later, [!INCLUDE ssSDS_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

Applies to: [!INCLUDE sssql22-md] and later versions, and [!INCLUDE ssSDS_md]

AS_TRANSACTION_ID_START
AS_TRANSACTION_ID_END
AS_SEQUENCE_NUMBER_START
AS_SEQUENCE_NUMBER_END
ledger_view_column_type tinyint 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 on database ledger, see Ledger.

Applies to: [!INCLUDE sssql22-md] and later versions, and [!INCLUDE ssSDS_md].
ledger_view_column_type_desc nvarchar(60) If not NULL, contains a textual description of the type of a column in a ledger view:

TRANSACTION_ID
SEQUENCE_NUMBER
OPERATION_TYPE
OPERATION_TYPE_DESC

Applies to: [!INCLUDE sssql22-md] and later versions, and [!INCLUDE ssSDS_md].
is_dropped_ledger_column bit Indicates a ledger table column that was dropped.

Applies to: [!INCLUDE sssql22-md] and later versions, and [!INCLUDE ssSDS_md]
vector_dimensions int Indicates how many dimensions the vector has.

Applies to: [!INCLUDE sssql25-md] and later versions, and [!INCLUDE ssSDS_md]
vector_base_type tinyint Indicates the data type used to store vector dimensions values.

0 = 32-bit (single-precision) float
1 = 16-bit (half-precision) float 1

Applies to: [!INCLUDE sssql25-md] and later versions, and [!INCLUDE ssSDS_md]
vector_base_type_desc nvarchar(10) Contains the textual description of the data type used to store vector dimensions values.

Applies to: [!INCLUDE sssql25-md] and later versions, and [!INCLUDE ssSDS_md]

1 For more information, see Half-precision floating-point format.

Permissions

[!INCLUDE ssCatViewPerm] For more information, see Metadata visibility configuration.

Related content