sys.columns (Transact-SQL)
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 function (FT)
- Inline table-valued SQL function (IF)
- Internal tables (IT)
- System tables (S)
- Table-valued SQL function (TF)
- User table (U)
- Views (V)
| 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 |
Reserved for future use. |
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 |
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). |
See Also
Reference
System Views (Transact-SQL)
Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
sys.all_columns (Transact-SQL)
sys.system_columns (Transact-SQL)
Other Resources
Querying the SQL Server System Catalog FAQ
Implementing User-defined Types
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
14 April 2006 |
|