sys-syscolumns-transact-sql.md

title	sys.syscolumns (Transact-SQL)
description	sys.syscolumns (Transact-SQL)
author	rwestMSFT
ms.author	randolphwest
ms.date	03/15/2017
ms.service	sql
ms.subservice	system-objects
ms.topic	reference
f1_keywords	sys.syscolumns sys.syscolumns_TSQL syscolumns_TSQL syscolumns
helpviewer_keywords	syscolumns system table sys.syscolumns compatibility view
dev_langs	TSQL
monikerRange	>=aps-pdw-2016||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||=fabric

sys.syscolumns (Transact-SQL)

[!INCLUDE sql-asdbmi-asa-pdw-fabricse-fabricdw]

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

Important

[!INCLUDEssnoteCompView]

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	[!INCLUDEssInternalOnly]
xusertype	smallint	ID of extended user-defined data type. Overflows or returns NULL if the number of data types exceeds 32,767.
length	smallint	Maximum physical storage length from sys.types.
xprec	tinyint	[!INCLUDEssInternalOnly]
xscale	tinyint	[!INCLUDEssInternalOnly]
colid	smallint	Column or parameter ID.
xoffset	smallint	[!INCLUDEssInternalOnly]
bitpos	tinyint	[!INCLUDEssInternalOnly]
reserved	tinyint	[!INCLUDEssInternalOnly]
colstat	smallint	[!INCLUDEssInternalOnly]
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	[!INCLUDEssInternalOnly]
autoval	varbinary(8000)	[!INCLUDEssInternalOnly]
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.
printfmt	varchar(255)	[!INCLUDEssInternalOnly]
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.

See Also

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