sys.columns (Transact-SQL)

Returns a row for each column in a user-defined table or view, and for each column in a system table.

Applies to

Syntax


SELECT
    [object_id]
,   [name]
,   [column_id]
,   [user_type_id]
,   [system_type_id]
,   [max_length]
,   [precision]
,   [scale]
,   [collation_name]
,   [is_nullable]
,   [is_ansi_padded]
,   [is_rowguidcol]
,   [is_identity]
,   [is_computed]
,   [is_filestream]
,   [is_sparse]
,   [is_column_set]
,   [generated_always_type]
,   [generated_always_type_desc]
,   [is_hidden]
,   [is_masked]
,   [graph_type]
,   [graph_type_desc]
FROM
    sys.columns
WHERE
    object_id = OBJECT_ID(N'MyTable');

Description

The sys.columns catalog view contains information about the columns of all user-defined tables and views, and system tables in a database. Each row in sys.columns represents one column.

Permissions

Members of the public role have permission to view the most common properties of columns. To retrieve properties such as is_identity, is_computed, and others, you require ALTER ANY LOGIN permission.

Return Value

The following table describes the columns in the sys.columns catalog view.

Column Name Data Type Description
object_id int Identifier of the object to which the column belongs.
name sysname Name of the column.
column_id int Identifier of the column within the object.
user_type_id int User-defined type ID of the column.
system_type_id tinyint System type ID of the column.
max_length smallint Maximum length, in bytes, of the column.
precision tinyint Precision of the column.
scale tinyint Scale of the column.
collation_name sysname Name of the collation of the column.
is_nullable bit 1 = Column can contain null values. 0 = Column cannot contain null values.
is_ansi_padded bit 1 = Column is ANSI padded. 0 = Column is not ANSI padded.
is_rowguidcol bit 1 = Column is a ROWGUIDCOL column. 0 = Column is not a ROWGUIDCOL column.
is_identity bit 1 = Column is an identity column. 0 = Column is not an identity column.
is_computed bit 1 = Column is a computed column. 0 = Column is not a computed column.
is_filestream bit 1 = Column is a FILESTREAM column. 0 = Column is not a FILESTREAM column.
is_sparse bit 1 = Column is a sparse column. 0 = Column is not a sparse column.
is_column_set bit 1 = Column is a column set. 0 = Column is not a column set.
generated_always_type tinyint Type of generated always column.
generated_always_type_desc nvarchar(60) Description of the generated always type.
is_hidden bit 1 = Column is hidden. 0 = Column is not hidden.
is_masked bit 1 = Column is masked. 0 = Column is not masked.
graph_type tinyint Type of graph column.
graph_type_desc nvarchar(60) Description of the graph type.

Note

For more information on data types and their properties, refer to the sys.types catalog view.

Example Usage

The following query retrieves the names, data types, and nullability of columns in a table named 'MyOrderedItems'.


SELECT
    c.name AS ColumnName,
    t.name AS DataType,
    c.max_length,
    c.is_nullable
FROM
    sys.columns AS c
JOIN
    sys.types AS t ON c.user_type_id = t.user_type_id
WHERE
    c.object_id = OBJECT_ID(N'Production.MyOrderedItems');

Important

Be cautious when querying system catalog views. Ensure your queries are efficient and do not impact the performance of your SQL Server instance.