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 2005SELECT [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 Tablessystem 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 2005SELECT [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