Export (0) Print
Expand All

sys.systypes (Transact-SQL)

Returns one row for each system-supplied and each user-defined data type defined in the database.

Important note 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 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.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Column name

Data type

Description

name

sysname

Data type name.

xtype

tinyint

Physical storage type.

status

tinyint

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

xusertype

smallint

Extended user type. Overflows or returns NULL if the number of data types exceeds 32,767.

length

smallint

Physical length of the data type.

xprec

tinyint

Internal precision, as used by the server. Not to be used in queries.

xscale

tinyint

Internal scale, as used by the server. Not to be used in queries.

tdefault

int

ID of the stored procedure that contains integrity checks for this data type.

domain

int

ID of the stored procedure that contains integrity checks for this data type.

uid

smallint

Schema 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 note 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.

reserved

smallint

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

collationid

int

If character based, collationid is the id of the collation of the current database; otherwise, it is NULL.

usertype

smallint

User type ID. Overflows or returns NULL if the number of data types exceeds 32,767.

variable

bit

Variable-length data type.

1 = True

0 = False

allownulls

bit

Indicates the default nullability for this data type. This default value is overridden by if nullability is specified by using CREATE TABLE or ALTER TABLE.

type

tinyint

Physical storage data type.

printfmt

varchar(255)

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

prec

smallint

Level of precision for this data type.

-1 = xml or large value types.

scale

tinyint

Scale for this data type, based on precision.

NULL = Data type is nonnumeric.

collation

sysname

If character based, collation is the collation of the current database; otherwise, it is NULL.

Community Additions

ADD
Show:
© 2014 Microsoft