sys.systypes (Transact-SQL)

 

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

Returns one row for each system-supplied and each user-defined data type defined 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
namesysnameData type name.
xtypetinyintPhysical storage type.
statustinyintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
xusertypesmallintExtended user type. Overflows or returns NULL if the number of data types exceeds 32,767.
lengthsmallintPhysical length of the data type.
xprectinyintInternal precision, as used by the server. Not to be used in queries.
xscaletinyintInternal scale, as used by the server. Not to be used in queries.
tdefaultintID of the stored procedure that contains integrity checks for this data type.
domainintID of the stored procedure that contains integrity checks for this data type.
uidsmallintSchema ID of the owner of the type.

For databases upgraded from an earlier version of SQL Server, the schema ID is equal to the user ID of the owner.

 ** Important ** If you use any of the following SQL Server DDL statements, you must use the sys.types catalog view instead of sys.systypes.

ALTER AUTHORIZATION ON TYPE

CREATE TYPE

Overflows or returns NULL if the number of users and roles exceeds 32,767.
reservedsmallintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
collationidintIf character based, collationid is the id of the collation of the current database; otherwise, it is NULL.
usertypesmallintUser type ID. Overflows or returns NULL if the number of data types exceeds 32,767.
variablebitVariable-length data type.

1 = True

0 = False
allownullsbitIndicates the default nullability for this data type. This default value is overridden by if nullability is specified by using CREATE TABLE or ALTER TABLE.
typetinyintPhysical storage data type.
printfmtvarchar(255)Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
precsmallintLevel of precision for this data type.

-1 = xml or large value types.
scaletinyintScale for this data type, based on precision.

NULL = Data type is nonnumeric.
collationsysnameIf character based, collation is the collation of the current database; otherwise, it is NULL.

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

Community Additions

ADD
Show: