Export (0) Print
Expand All
Expand Minimize

sys.columns (Transact-SQL)

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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)

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

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

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

Nonzero 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_id

int

ID 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_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 Sparse Columns.

generated_always_type

tinyint

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

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.

generated_always_type_desc

nvarchar(60)

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

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.

is_hidden

bit

Indicates if period column is hidden:

0: regular, not-hidden, visible column

1: hidden column

Applies to: SQL Server 2016 CTP 2.1 through SQL Server 2016.

encryption_type

int

Encryption type:

1 – Deterministic encryption

2 – Randomized encryption

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.

encryption_type_desc

nvarchar(64)

Encryption type description:

RANDOMIZED

DETERMINISTIC

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.

encryption_algorithm_name

sysname

Name of encryption algorithm.

Only AEAD_AES_256_CBC_HMAC_SHA_512 is supported.

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.

column_encryption_key_id

int

ID of the CEK.

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.

column_encryption_key_database_name

sysname

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.

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft