Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.types

 

This is a view in the mssqlsystemresource database.

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 of the type.

user_type_id

int

ID of the type. Is unique within the database. For system data types, user_type_id = system_type_id.

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.

 




Syntax
CREATE VIEW sys.types AS
	SELECT name,
		t.xtype AS system_type_id,
		t.id AS user_type_id,
		t.schid AS schema_id,
		o.indepid AS principal_id,
		t.length AS max_length,
		t.prec AS precision,
		t.scale AS scale,
		convert(sysname, collationpropertyfromid(t.collationid, 'name')) AS collation_name,
		sysconv(bit, 1 - (t.status & 1)) AS is_nullable,	-- TYPE_NOTNULL
		sysconv(bit, case when t.id > 256 then 1 else 0 end) AS is_user_defined,	-- x_utypSSNAME
		sysconv(bit, case when t.xtype = 240 then 1 else 0 end) AS is_assembly_type,	-- XVT_UDT
		t.dflt AS default_object_id,
		t.chk AS rule_object_id,
		sysconv(bit, case when t.xtype = 243 then 1 else 0 end) AS is_table_type	-- XVT_TABLETYPE
	FROM sys.sysscalartypes t
	LEFT JOIN sys.syssingleobjrefs o ON o.depid = t.id AND o.class = 44 AND o.depsubid = 0	-- SRC_TYPETOOWNER
	WHERE t.id <= 256 OR has_access('UT', t.id) = 1

 
Last revision 2008RTM
See also

  assembly_types (View)
COLUMNS (View)
COLUMN_DOMAIN_USAGE (View)
DOMAINS (View)
DOMAIN_CONSTRAINTS (View)
event_notification_event_types (View)
fulltext_document_types (View)
objects$ (View)
PARAMETERS (View)
ROUTINE_COLUMNS (View)
service_message_types (View)
service_message_types$ (View)
spt_columns_odbc_view (View)
spt_columns_view (View)
spt_columns_view_managed (View)
spt_datatype_info_view (View)
spt_procedure_params_managed_view (View)
spt_procedure_params_view (View)
spt_provider_types_view (View)
spt_sparse_columns_view (View)
spt_sproc_columns_odbc_view (View)
spt_tablecollations_view (View)
spt_table_types_view (View)
spt_table_type_columns_view (View)
spt_table_type_primary_keys_view (View)
sp_addarticle (Procedure)
sp_adddatatypemapping (Procedure)
sp_addmergearticle (Procedure)
sp_addmergepublication (Procedure)
sp_addtype (Procedure)
sp_bindefault (Procedure)
sp_bindrule (Procedure)
sp_changemergearticle (Procedure)
sp_checkOraclepackageversion (Procedure)
sp_check_removable_sysusers (Procedure)
sp_columns_ex (Procedure)
sp_columns_ex_100 (Procedure)
sp_columns_ex_90 (Procedure)
sp_datatype_info (Procedure)
sp_datatype_info_90 (Procedure)
sp_dropdatatype (Procedure)
sp_dropdatatypemapping (Procedure)
sp_droptype (Procedure)
sp_ExternalMailQueueListener (Procedure)
sp_getOraclepackageversion (Procedure)
sp_getqueuedrows (Procedure)
sp_help (Procedure)
sp_IHaddarticle (Procedure)
sp_IHaddpublisher (Procedure)
sp_IHarticlecolumn (Procedure)
sp_IHflushmetadata (Procedure)
sp_IHget_colinfo (Procedure)
sp_IHscripttable (Procedure)
sp_IHscriptupdateparams (Procedure)
sp_IHsyncmetadata (Procedure)
sp_MSaddanonymousreplica (Procedure)
sp_MSaddautonosyncsubscription (Procedure)
sp_MSadd_merge_partition_column (Procedure)
sp_MSadd_subscriber_info (Procedure)
sp_MSbuild_single_post (Procedure)
sp_MScreate_dist_tables (Procedure)
sp_MScreate_sub_tables_internal (Procedure)
sp_MSdelsubrowsbatch (Procedure)
sp_MSdependencies (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MSenumallpublications (Procedure)
sp_MSenumdeletesmetadata (Procedure)
sp_MSenumschemachange_100 (Procedure)
sp_MSenumschemachange_70 (Procedure)
sp_MSenumschemachange_80 (Procedure)
sp_MSenumschemachange_80sp3 (Procedure)
sp_MSenumschemachange_90 (Procedure)
sp_MSfixup_single_artddls (Procedure)
sp_MSfixup_single_ddls (Procedure)
sp_MSgettrancftsrcrow (Procedure)
sp_MSget_colinfo (Procedure)
sp_MSget_effective_pub_compat_level (Procedure)
sp_MSget_repl_commands (Procedure)
sp_MSget_type (Procedure)
sp_MSget_type_wrapper (Procedure)
sp_MShelpalterbeforetable (Procedure)
sp_MShelpcolumns (Procedure)
sp_MShelpcreatebeforetable (Procedure)
sp_MShelptype (Procedure)
sp_MShelp_replication_table (Procedure)
sp_MSmakebatchinsertproc (Procedure)
sp_MSmakebatchupdateproc (Procedure)
sp_MSmakeconflictinsertproc (Procedure)
sp_MSmakeconflicttable (Procedure)
sp_MSmakeinsertproc (Procedure)
sp_MSmakepeerconflicttable (Procedure)
sp_MSmakeupdateproc (Procedure)
sp_MSmaptype (Procedure)
sp_MSmap_subscriber_type (Procedure)
sp_MSmerge_altertable (Procedure)
sp_MSrepl_addarticle (Procedure)
sp_MSrepl_adddatatypemapping (Procedure)
sp_MSrepl_addsubscription (Procedure)
sp_MSrepl_articlecolumn (Procedure)
sp_MSrepl_changearticlecolumndatatype (Procedure)
sp_MSrepl_changesubstatus (Procedure)
sp_MSrepl_createdatatypemappings (Procedure)
sp_MSrepl_DB2datatypes (Procedure)
sp_MSrepl_enable_articles_for_het_sub (Procedure)
sp_MSrepl_helparticle (Procedure)
sp_MSrepl_helparticlecolumns (Procedure)
sp_MSrepl_helpsubscription (Procedure)
sp_MSrepl_MSSQLdatatypes (Procedure)
sp_MSrepl_ORAdatatypes (Procedure)
sp_MSrepl_reinitsubscription (Procedure)
sp_MSrepl_SASdatatypes (Procedure)
sp_MSscript_compensating_insert (Procedure)
sp_MSscript_params (Procedure)
sp_MSscript_trigger_variables (Procedure)
sp_MSscript_update_statement (Procedure)
sp_MSset_dynamic_filter_options (Procedure)
sp_MStablekeys (Procedure)
sp_MStable_not_modifiable (Procedure)
sp_MStran_altertable (Procedure)
sp_MSvalidatepeertopeerarticles (Procedure)
sp_ORAhelparticle (Procedure)
sp_provider_types_100_rowset (Procedure)
sp_provider_types_90_rowset (Procedure)
sp_provider_types_rowset (Procedure)
sp_rename (Procedure)
sp_repladdcolumn (Procedure)
sp_replscriptuniquekeywhereclause (Procedure)
sp_scriptdelproccore (Procedure)
sp_scriptinsproccore (Procedure)
sp_scriptpubwinsrefreshcursorvars (Procedure)
sp_scriptupdateparams (Procedure)
sp_scriptupdproccore (Procedure)
sp_send_dbmail (Procedure)
sp_setOraclepackageversion (Procedure)
sp_special_columns (Procedure)
sp_special_columns_100 (Procedure)
sp_special_columns_90 (Procedure)
sp_sqlagent_get_perf_counters (Procedure)
sp_sqlagent_notify (Procedure)
sp_syscollector_create_collection_item (Procedure)
sp_syscollector_create_collector_type (Procedure)
sp_syscollector_delete_collector_type (Procedure)
sp_syscollector_update_collector_type (Procedure)
sp_syscollector_validate_xml (Procedure)
sp_syscollector_verify_collector_type (Procedure)
sp_syspolicy_dispatch_event (Procedure)
sp_syspolicy_set_config_enabled (Procedure)
sp_syspolicy_update_ddl_trigger (Procedure)
sp_syspolicy_update_event_notification (Procedure)
sp_sysutility_mi_create_cache_directory (Procedure)
sp_sysutility_ucp_get_policy_violations (Procedure)
sp_tableoption (Procedure)
sp_tables (Procedure)
sp_table_types (Procedure)
sp_table_types_rowset (Procedure)
sp_table_type_columns_100 (Procedure)
sp_unbindefault (Procedure)
sp_unbindrule (Procedure)
sp_usertypes_rowset (Procedure)
sp_usertypes_rowset2 (Procedure)
sp_usertypes_rowset_rmt (Procedure)
sp_vupgrade_distdb (Procedure)
sp_vupgrade_publisherdb (Procedure)
sql_expression_dependencies$ (View)
sysaltfiles (View)
syscollector_collector_types (View)
syscolumns (View)
sysfiles (View)
systypes (View)
sysutility_ucp_dac_policy_type (View)
sysutility_ucp_instance_policy_type (View)
tables (View)
table_types (View)
trigger_event_types (View)
xml_schema_types (View)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash