Share via


sys.all_columns (Transact-SQL)

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 SQL Server 2005 Integration Services (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).

See Also

Reference

Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
sys.columns (Transact-SQL)
sys.system_columns (Transact-SQL)
sys.computed_columns (Transact-SQL)

Other Resources

Querying the SQL Server System Catalog FAQ

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006

New content:
  • In the description for user_type_id, added information about how to return the name of the type.