sys.all_columns (Transact-SQL)
TOC
Collapse the table of content
Expand the table of content

sys.all_columns (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Shows the union of all columns belonging to user-defined objects and system objects.

Column nameData typeDescription
object_idintID of the object to which this column belongs.
namesysnameName of the column. Is unique within the object.
column_idintID of the column. Is unique within the object.

Column IDs might not be sequential.
system_type_idtinyintID of the system-type of the column.
user_type_idintID 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_lengthsmallintMaximum 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'.
precisiontinyintPrecision of the column if numeric-based; otherwise, 0.
scaletinyintScale of the column if numeric-based; otherwise, 0.
collation_namesysnameName of the collation of the column if character-based; otherwise, NULL.
is_nullablebit1 = Column is nullable.
is_ansi_paddedbit1 = Column uses ANSI_PADDING ON behavior if character, binary, or variant.

0 = Column is not character, binary, or variant.
is_rowguidcolbit1 = Column is a declared ROWGUIDCOL.
is_identitybit1 = Column has identity values
is_computedbit1 = Column is a computed column.
is_filestreambit1 = Column is declared to use filestream storage.
is_replicatedbit1 = Column is replicated.
is_non_sql_subscribedbit1 = Column has a non-SQL Server subscriber.
is_merge_publishedbit1 = Column is merge-published.
is_dts_replicatedbit1 = Column is replicated by using SSIS.
is_xml_documentbit1 = Content is a complete XML document.

0 = Content is a document fragment, or the column data type is not XML.
xml_collection_idintNon-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_idintID 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_idintID 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_sparsebit1 = Column is a sparse column. For more information, see Use Sparse Columns.
is_column_setbit1 = Column is a column set. For more information, see Use Column Sets.
generated_always_typetinyintApplies to: SQL Server 2016 through SQL Server 2016.

The numeric value representing the type of column:

0 = NOT_APPLICABLE

1 = AS_ROW_START

2 = AS_ROW_END
generated_always_type_descnvarchar(60)Applies to: SQL Server 2016 through SQL Server 2016.

The text description of the type of column:

NOT_APPLICABLE

AS_ROW_START

AS_ROW_END

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. 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)

Community Additions

ADD
Show:
© 2016 Microsoft