sys.syscolumns (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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

System_CAPS_ICON_important.jpg Important


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 System Tables to System Views (Transact-SQL). 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 nameData typeDescription
namesysnameName of the column or procedure parameter.
idintObject ID of the table to which this column belongs, or the ID of the stored procedure with which this parameter is associated.
xtypetinyintPhysical storage type from sys.types.
typestattinyintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
xusertypesmallintID of extended user-defined data type. Overflows or returns NULL if the number of data types exceeds 32,767.
lengthsmallintMaximum physical storage length from sys.types.
xprectinyintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
xscaletinyintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
colidsmallintColumn or parameter ID.
xoffsetsmallintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
bitpostinyintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
reservedtinyintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
colstatsmallintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
cdefaultintID of the default for this column.
domainintID of the rule or CHECK constraint for this column.
numbersmallintSubprocedure number when the procedure is grouped.

0 = Nonprocedure entries
colordersmallintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
autovalvarbinary(8000)Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
offsetsmallintOffset into the row in which this column appears.
collationidintID of the collation of the column. NULL for noncharacter-based columns.
statustinyintBitmap 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.
typetinyintPhysical storage type from sys.types.
usertypesmallintID of user-defined data type from sys.types. Overflows or returns NULL if the number of data types exceeds 32,767.
printfmtvarchar(255)Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
precsmallintLevel of precision for this column.

-1 = xml or large value type.
scaleintScale for this column.

NULL = Data type is nonnumeric.
iscomputedintFlag indicating whether the column is computed:

0 = Noncomputed

1 = Computed
isoutparamintIndicates whether the procedure parameter is an output parameter:

1 = True

0 = False
isnullableintIndicates whether the column allows null values:

1 = True

0 = False
collationsysnameName of the collation of the column. NULL if not a character-based column.

Mapping System Tables to System Views (Transact-SQL)
Compatibility Views (Transact-SQL)

Community Additions

ADD
Show: