Implementing User-defined Types

This topic describes how to create and drop common language runtime (CLR) user-defined types in SQL Server.

Creating User-defined Types

To create a user-defined type in SQL Server, the following steps must be performed in order:

• Define the user-defined type as a class or structure in a language supported by the Microsoft .NET Framework. For more information about how to program types in the CLR, see CLR User-Defined Types. Then, compile the class or structure to build an assembly in the .NET Framework using the appropriate language compiler.
• Register the assembly in SQL Server by using the CREATE ASSEMBLY statement. For more information about assemblies in SQL Server, see Assemblies (Database Engine).
• Create the type that references the registered assembly.

To create, modify, or drop an assembly

• CREATE ASSEMBLY (Transact-SQL)
• ALTER ASSEMBLY (Transact-SQL)
• DROP ASSEMBLY (Transact-SQL)

To create a user-defined type

• CREATE TYPE (Transact-SQL)

Dropping User-defined Types

To drop a user-defined type

• DROP TYPE (Transact-SQL)

You cannot drop a user-defined type until all references to that type have been removed. These references can include the following:

• Columns defined on the type.
• Computed columns and CHECK constraints whose expressions reference the type.
• Schema-bound views and functions with expressions in their definitions that reference the type.
• Parameters of functions and stored procedures.

To find columns dependent on a user-defined type

• sys.columns (Transact-SQL)

The following example retrieves metadata about columns defined on user-defined type ComplexNumber.

SELECT * FROM sys.columns 
WHERE user_type_id = TYPE_ID('ComplexNumber');

• sys.column_type_usages

The following example retrieves limited metadata for least-privileged users about columns defined on user-defined type ComplexNumber.

SELECT * FROM sys.column_type_usages 
WHERE user_type_id = TYPE_ID('ComplexNumber');

To find computed column expressions, CHECK constraint expressions, and schema-bound view and function expressions dependent on a user-defined type

• sys.sql_dependencies (Transact-SQL)
• sys.objects (Transact-SQL)

The following example retrieves the names of computed columns (and their tables) with a dependency on user-defined type ComplexNumber.

SELECT OBJECT_NAME(object_id) AS object_name,
    COL_NAME(object_id, column_id) AS column_name
FROM sys.sql_dependencies
WHERE referenced_major_id = TYPE_ID('ComplexNumber')
    AND class = 2 
    AND OBJECTPROPERTY(object_id, 'IsTable')=1; 

The following example retrieves the names of CHECK constraints (and the objects on which they are defined) with a dependency on user-defined type ComplexNumber.

SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
    OBJECT_NAME(o.parent_object_id) AS table_name,
    OBJECT_NAME(o.object_id) AS constraint_name
FROM sys.sql_dependencies AS d
JOIN sys.objects AS o
ON o.object_id = d.object_id
WHERE referenced_major_id = TYPE_ID('ComplexNumber')
AND class = 2 
AND OBJECTPROPERTY(o.object_id, 'IsCheckCnst')=1;

The following example retrieves the names of schema-bound functions and views whose definitions reference user-defined type ComplexNumber.

SELECT SCHEMA_NAME(o.schema_id) AS dependent_object_schema,
    OBJECT_NAME(o.object_id) AS dependent_object_name,
    o.type_desc AS dependent_object_type,
    d.class_desc AS kind_of_dependency,
    d.referenced_major_id AS referenced_object
FROM sys.sql_dependencies AS d 
JOIN sys.objects AS o
    ON d.object_id = o.object_id
        AND o.type IN ('FN','IF','TF', 'V')
WHERE  d.class = 2
    AND d.referenced_major_id IN (TYPE_ID('ComplexNumber'))
ORDER BY dependent_object_schema, dependent_object_name;

To find parameters dependent on a user-defined type

• sys.parameters (Transact-SQL)

The following example retrieves the names of parameters (and the objects to which they belong) defined on user-defined type ComplexNumber.

SELECT OBJECT_NAME(object_id) AS object_name,
    NULL AS procedure_number,
    name AS param_name,
    parameter_id AS param_num,
    TYPE_NAME(p.user_type_id) AS type_name
FROM sys.parameters AS p
WHERE p.user_type_id = TYPE_ID('ComplexNumber')
ORDER BY object_name, procedure_number, param_num;

• sys.parameter_type_usages

The following example retrieves limited metadata for least-privileged users about parameters defined on user-defined type ComplexNumber.

SELECT * FROM sys.parameter_type_usages 
WHERE user_type_id = TYPE_ID('ComplexNumber');

See Also

Concepts

Working with CLR User-defined Types

Other Resources

CLR Programmability Samples

Help and Information

Getting SQL Server 2005 Assistance

Change History