| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | |||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.all_columns (Transact-SQL) |
sys.all_columns (Transact-SQL) |
rwestMSFT |
randolphwest |
05/24/2022 |
sql |
system-objects |
reference |
|
|
|
|
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]
Shows the union of all columns belonging to user-defined objects and system objects.
| 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 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 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 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 declared to use filestream storage. |
| is_replicated | bit | 1 = Column is replicated. |
| is_non_sql_subscribed | bit | 1 = Column has a non-[!INCLUDEssNoVersion] subscriber. |
| is_merge_published | bit | 1 = Column is merge-published. |
| is_dts_replicated | bit | 1 = Column is replicated by using [!INCLUDEssIS]. |
| 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 | Non-zero if the column's data type is xml and the XML is typed. The value will be the ID of the collection containing the column's validating XML schema namespace 0 = no XML schema collection. |
| default_object_id | int | ID of the default object, regardless of whether it is a stand-alone sys.sp_bindefault, or an in-line, 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). |
| 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 | Applies to: [!INCLUDEsssql16-md] and later, [!INCLUDEssSDS_md]. 7, 8, 9, 10 only applies to [!INCLUDEssSDS_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). |
| generated_always_type_desc | nvarchar(60) | Applies to: [!INCLUDEsssql16-md] and later, [!INCLUDEssSDS_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: Starting with [!INCLUDE sssql22-md], [!INCLUDEssSDS_md] AS_TRANSACTION_ID_START AS_TRANSACTION_ID_END AS_SEQUENCE_NUMBER_START AS_SEQUENCE_NUMBER_END |
| ledger_view_column_type | tinyint | Applies to: Starting with [!INCLUDE sssql22-md], [!INCLUDEssSDS_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 For more information on database ledger, see Ledger. |
| ledger_view_column_type_desc | nvarchar(60) | Applies to: Starting with [!INCLUDE sssql22-md], [!INCLUDEssSDS_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 |
[!INCLUDEssCatViewPerm] For more information, see Metadata Visibility Configuration.
Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
Querying the SQL Server System Catalog FAQ
sys.columns (Transact-SQL)
sys.system_columns (Transact-SQL)
sys.computed_columns (Transact-SQL)