December 6, 2012

Scalar Types Catalog Views

Se more view version maps here: Microsoft Sql System View Version Maps

sys.assembly_types

user-defined type that is defined by a CLR assembly. The following sys.assembly_types appear in the list of inherited columns (see sys.types (Transact-SQL)1) after rule_object_id.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
name sysname sys.types Name of the type. Is unique within the schema.
system_type_id tinyint sys.types ID of the internal system-type of the type.
user_type_id int sys.types ID of the type. Is unique within the database. For system data types, user_type_id = system_type_id.
schema_id int sys.types ID of the schema to which the type belongs.
principal_id int sys.types 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 sys.types 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 sys.types Max precision of the type if it is numeric-based; otherwise, 0.
scale tinyint sys.types Max scale of the type if it is numeric-based; otherwise, 0.
collation_name sysname sys.types Name of the collation of the type if it is character-based; other wise, NULL.
is_nullable bit sys.types Type is nullable.
is_user_defined bit sys.types 1 = User-defined type.
0 = SQL Server 2005 system data type.
is_assembly_type bit sys.types 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 sys.types ID of the stand-alone default that is bound to the type by using sp_bindefault1.
0 = No default exists.
rule_object_id int sys.types ID of the stand-alone rule that is bound to the type by using sp_bindrule2.
0 = No rule exists.
assembly_id int   ID of the assembly from which this type was created.
assembly_class sysname   Name of the class within the assembly that defines this type.
is_binary_ordered bit   Sorting the bytes of this type is equivalent to sorting using comparison operators on the type.
is_fixed_length bit   Length of the type is always the same as max_length.
prog_id nvarchar(40)   ProgID of the type as exposed to COM.
assembly_qualified_name nvarchar(4000)   Assembly qualified type name. The name is in a format suitable to be passed to Type.GetType().
is_table_type   bit sys.types Indicates the type is a table.

TSQL

Sql 2005
SELECT [name], [system_type_id], [user_type_id], [schema_id], [principal_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_user_defined], [is_assembly_type], [default_object_id], [rule_object_id], [assembly_id], [assembly_class], [is_binary_ordered], [is_fixed_length], [prog_id], [assembly_qualified_name] FROM sys.assembly_types
Sql 2008
SELECT [name], [system_type_id], [user_type_id], [schema_id], [principal_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_user_defined], [is_assembly_type], [default_object_id], [rule_object_id], [is_table_type], [assembly_id], [assembly_class], [is_binary_ordered], [is_fixed_length], [prog_id], [assembly_qualified_name] FROM sys.assembly_types
Sql 2008 R2
SELECT [name], [system_type_id], [user_type_id], [schema_id], [principal_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_user_defined], [is_assembly_type], [default_object_id], [rule_object_id], [is_table_type], [assembly_id], [assembly_class], [is_binary_ordered], [is_fixed_length], [prog_id], [assembly_qualified_name] FROM sys.assembly_types
Sql 2012
SELECT [name], [system_type_id], [user_type_id], [schema_id], [principal_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_user_defined], [is_assembly_type], [default_object_id], [rule_object_id], [is_table_type], [assembly_id], [assembly_class], [is_binary_ordered], [is_fixed_length], [prog_id], [assembly_qualified_name] FROM sys.assembly_types

Back to Top



sys.types

If you want to learn more about SQL Types and mapping to .NET CLR look here CLR Datatype Mapping Tables
system and user-defined type.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 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 2005 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_bindefault1.
0 = No default exists.
rule_object_id int ID of the stand-alone rule that is bound to the type by using sp_bindrule2.
0 = No rule exists.
is_table_type   bit Indicates the type is a table.

TSQL

Sql 2005
SELECT [name], [system_type_id], [user_type_id], [schema_id], [principal_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_user_defined], [is_assembly_type], [default_object_id], [rule_object_id] FROM sys.types
Sql 2008
SELECT [name], [system_type_id], [user_type_id], [schema_id], [principal_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_user_defined], [is_assembly_type], [default_object_id], [rule_object_id], [is_table_type] FROM sys.types
Sql 2008 R2
SELECT [name], [system_type_id], [user_type_id], [schema_id], [principal_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_user_defined], [is_assembly_type], [default_object_id], [rule_object_id], [is_table_type] FROM sys.types
Sql 2012
SELECT [name], [system_type_id], [user_type_id], [schema_id], [principal_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_user_defined], [is_assembly_type], [default_object_id], [rule_object_id], [is_table_type] FROM sys.types

Back to Top

No comments:

Post a Comment

Total Pageviews