Export (0) Print
Expand All
6 out of 11 rated this helpful - Rate this topic

sys.syscolumns (Transact-SQL)

Returns one row for every column in every table and view, and a row for each parameter in a stored procedure in the database.

ms186816.note(en-US,SQL.90).gifImportant:
This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name Data type Description

name

sysname

Name of the column or procedure parameter.

id

int

Object ID of the table to which this column belongs, or the ID of the stored procedure with which this parameter is associated.

xtype

tinyint

Physical storage type from sys.types.

typestat

tinyint

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

xusertype

smallint

ID of extended user-defined data type. Overflows or returns NULL if the number of data types exceeds 32,767. For more information, see Querying the SQL Server System Catalog.

length

smallint

Maximum physical storage length from sys.types.

xprec

tinyint

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

xscale

tinyint

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

colid

smallint

Column or parameter ID.

xoffset

smallint

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

bitpos

tinyint

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

reserved

tinyint

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

colstat

smallint

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

cdefault

int

ID of the default for this column.

domain

int

ID of the rule or CHECK constraint for this column.

number

smallint

Subprocedure number when the procedure is grouped.

0 = Nonprocedure entries

colorder

smallint

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

autoval

varbinary(8000)

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

offset

smallint

Offset into the row in which this column appears.

collationid

int

ID of the collation of the column. NULL for noncharacter-based columns.

status

tinyint

Bitmap used to describe a property of the column or the parameter:

0x08 = Column allows null values.

0x10 = ANSI padding was in effect when varchar or varbinary columns were added. Trailing blanks are preserved for varchar and trailing zeros are preserved for varbinary columns.

0x40 = Parameter is an OUTPUT parameter.

0x80 = Column is an identity column.

type

tinyint

Physical storage type from sys.types.

usertype

smallint

ID of user-defined data type from sys.types. Overflows or returns NULL if the number of data types exceeds 32,767. For more information, see Querying the SQL Server System Catalog.

printfmt

varchar(255)

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

prec

smallint

Level of precision for this column.

-1 = xml or large value type.

scale

int

Scale for this column.

NULL = Data type is nonnumeric.

iscomputed

int

Flag indicating whether the column is computed:

0 = Noncomputed

1 = Computed

isoutparam

int

Indicates whether the procedure parameter is an output parameter:

1 = True

0 = False

isnullable

int

Indicates whether the column allows null values:

1 = True

0 = False

collation

sysname

Name of the collation of the column. NULL if not a character-based column.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.