sys.types (Transact-SQL)


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

Contains a row for each system and user-defined type.

Column nameData typeDescription
namesysnameName of the type. Is unique within the schema.
system_type_idtinyintID of the internal system-type of the type.
user_type_idintID of the type. Is unique within the database. For system data types, user_type_id = system_type_id.
schema_idintID of the schema to which the type belongs.
principal_idintID of the individual owner if different from schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership.

NULL if there is no alternate individual owner.
max_lengthsmallintMaximum length (in bytes) of the type.

-1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml.

For text columns, the max_length value will be 16.
precisiontinyintMax precision of the type if it is numeric-based; otherwise, 0.
scaletinyintMax scale of the type if it is numeric-based; otherwise, 0.
collation_namesysnameName of the collation of the type if it is character-based; other wise, NULL.
is_nullablebitType is nullable.
is_user_definedbit1 = User-defined type.

0 = SQL Server system data type.
is_assembly_typebit1 = Implementation of the type is defined in a CLR assembly.

0 = Type is based on a SQL Server system data type.
default_object_idintID of the stand-alone default that is bound to the type by using sp_bindefault.

0 = No default exists.
rule_object_idintID of the stand-alone rule that is bound to the type by using sp_bindrule.

0 = No rule exists.
is_table_typebitIndicates the type is a table.

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

Catalog Views (Transact-SQL)
Scalar Types Catalog Views (Transact-SQL)
Querying the SQL Server System Catalog FAQ

Community Additions