Edit

Share via

sys.types (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

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

Column name Data type Description
name sysname Name of the type. Is unique within the schema.
system_type_id tinyint ID of the internal system type.
user_type_id int ID of the type. Is unique within the database.

CLR assembly types such as hierarchyid, geometry and geography, will have a different system_type_id, and can be identified using is_assembly_type. The sysname data type is an internal data type based on nvarchar.
schema_id int ID of the schema to which the type belongs.
principal_id int ID 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_length smallint Maximum 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.
precision tinyint Max precision of the type if it is numeric-based; otherwise, 0.
scale tinyint Max scale of the type if it is numeric-based; otherwise, 0.
collation_name sysname Name of the collation of the type if it is character-based; other wise, NULL.
is_nullable bit Type is nullable.
is_user_defined bit 1 = User-defined type.

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

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

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

0 = No rule exists.
is_table_type bit Indicates the type is a table.

Permissions

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

Usage examples

Get column details for a table

To get metadata for columns in a table you can use the following code:

CREATE TABLE dbo.[sample] (
    id INT NOT NULL
    ,col1 VARBINARY(10) NULL
    )
GO

SELECT c.[name] AS column_name
    ,t.[name] AS [type_name]
    ,c.[max_length]
    ,c.[precision]
    ,c.[scale]
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE object_id = object_id('dbo.sample');

Related content