TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sys.columns (Transact-SQL)

 

Updated: October 29, 2015

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

Returns a row for each column of an object that has columns, such as views or tables. The following is a list of 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 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 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 a FILESTREAM column.
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_idintNonzero if the data type of the column is xml and the XML is typed. The value will be the ID of the collection containing the validating XML schema namespace of the column.

0 = No XML schema collection.
default_object_idintID of the default object, regardless of whether it is a stand-alone object 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.

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 Sparse Columns.
generated_always_typetinyintApplies to: SQL Server 2016 Release Candidate (RC3) through SQL Server 2016.

The numeric value representing the type of column (value will always be 0 for columns in system tables):

0 = NOT_APPLICABLE

1 = AS_ROW_START

2 = AS_ROW_END

For more information about Always Encrypted, see Always Encrypted (Database Engine).
generated_always_type_descnvarchar(60)Applies to: SQL Server 2016 Release Candidate (RC3) through SQL Server 2016.

The text description of the type of column (value will always be NOT_APPLICABLE for columns in system tables):

NOT_APPLICABLE

AS_ROW_START

AS_ROW_END
is_hiddenbitApplies to: SQL Server 2016 through SQL Server 2016.

Indicates if period column is hidden:

0: regular, not-hidden, visible column

1: hidden column
encryption_typeintApplies to: SQL Server 2016 Release Candidate (RC3) through SQL Server 2016.

Encryption type:

1 – Deterministic encryption

2 – Randomized encryption
encryption_type_descnvarchar(64)Applies to: SQL Server 2016 Release Candidate (RC3) through SQL Server 2016.

Encryption type description:

RANDOMIZED

DETERMINISTIC
encryption_algorithm_namesysnameApplies to: SQL Server 2016 Release Candidate (RC3) through SQL Server 2016.

Name of encryption algorithm.

Only AEAD_AES_256_CBC_HMAC_SHA_512 is supported.
column_encryption_key_idintApplies to: SQL Server 2016 Release Candidate (RC3) through SQL Server 2016.

ID of the CEK.
column_encryption_key_database_namesysnameApplies to: SQL Server 2016 Release Candidate (RC3) through SQL Server 2016.

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.

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.

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

Community Additions

ADD
Show:
© 2016 Microsoft