sys.all_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
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-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 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: SQL Server 2016 (13.x) and later, SQL Database. 7, 8, 9, 10 only applies to SQL Database. 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: SQL Server 2016 (13.x) and later, SQL Database. 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 SQL Server 2022 (16.x), SQL Database 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 SQL Server 2022 (16.x), SQL Database. 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 SQL Server 2022 (16.x), SQL Database. 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
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.
See Also
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)