Implémentation de types définis par l'utilisateur

Mis à jour : 14 avril 2006

Cette rubrique explique comment créer et supprimer des types définis par l'utilisateur CLR (Common Language Runtime) dans SQL Server.

Création de types définis par l'utilisateur

Pour créer un type défini par l'utilisateur dans SQL Server, exécutez la procédure suivante :

• Définissez le type défini par l'utilisateur en tant que classe ou structure dans un langage pris en charge par Microsoft .NET Framework. Pour plus d'informations sur la programmation de types dans le CLR, consultez CLR User-Defined Types. Compilez ensuite la classe ou la structure afin de créer un assembly dans le .NET Framework à l'aide du compilateur de langage adéquat.
• Inscrivez l'assembly dans SQL Server à l'aide de l'instruction CREATE ASSEMBLY. Pour plus d'informations sur les assemblys dans SQL Server, consultez Assemblys (moteur de base de données).
• Créez le type qui fait référence à l'assembly inscrit.

Remarque :
Le déploiement d'un projet SQL Server dans Microsoft Visual Studio enregistre un assembly dans la base de données spécifiée pour le projet. Le déploiement du projet crée aussi les types CLR définis par l'utilisateur dans la base de données pour toutes les définitions de classe annotées par l'attribut SqlUserDefinedType. Pour plus d'informations, consultez Deploying CLR Database Objects.

Remarque :
Par défaut, la capacité de SQL Server à exécuter du code CLR est définie à la valeur OFF. Vous pouvez créer, modifier ou supprimer des objets de base de données qui référencent des modules de code managé, mais ces références ne seront pas exécutées dans SQL Server à moins que l'option clr enabled ne soit activée en utilisant sp_configure.

Pour créer, modifier ou supprimer un assembly

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

Pour créer un type défini par l'utilisateur

• CREATE TYPE (Transact-SQL)

Suppression de types définis par l'utilisateur

Pour supprimer un type défini par l'utilisateur

• DROP TYPE (Transact-SQL)

Remarque :
Les types définis par l'utilisateur ne peuvent pas être modifiés après leur création, car toute modification rendrait les données des tables ou des index non valides. Pour modifier un type, vous devez soit le supprimer puis le recréer, soit émettre une instruction ALTER ASSEMBLY à l'aide de la clause WITH UNCHECKED DATA.

Vous ne pouvez pas supprimer un type défini par l'utilisateur tant que toutes les références à ce type n'ont pas été supprimées. Elles incluent notamment :

• les colonnes définies sur ce type ;
• les colonnes calculées et les contraintes CHECK dont les expressions font référence au type ;
• les vues et les fonctions liées au schéma et dont les définitions contiennent des expressions qui font référence au type ;
• les paramètres des fonctions et des procédures stockées.

Pour rechercher des colonnes dépendant d'un type d'utilisateur défini

• sys.columns (Transact-SQL)

L'exemple suivant extrait des métadonnées sur les colonnes définies sur le type défini par l'utilisateur ComplexNumber.

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

• sys.column_type_usages

L'exemple suivant extrait des métadonnées limitées pour les utilisateurs détenant des privilèges minimaux sur les colonnes définies sur le type défini par l'utilisateur ComplexNumber.

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

Pour rechercher des expressions de colonne calculée, des expressions de contrainte CHECK et des expressions de vue et de fonction liées à un schéma dépendant d'un type défini par l'utilisateur

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

L'exemple suivant extrait les noms des colonnes calculées (et leurs tables) dépendant d'un type défini par l'utilisateur 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; 

L'exemple suivant extrait les noms des contraintes CHECK (et les objets sur lesquels ils sont définis) dépendant d'un type défini par l'utilisateur 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;

L'exemple suivant extrait les noms des fonctions et des vues liées à un schéma dont les définitions font référence au type défini par l'utilisateur ComplexNumber.

Remarque :
SQL Server ne gère pas les métadonnées de dépendance entre les types définis par l'utilisateur et leur utilisation dans les corps des procédures stockées, des déclencheurs, des fonctions et des vues non liées à un schéma.

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;

Pour rechercher des paramètres dépendant d'un type défini par l'utilisateur

• sys.parameters (Transact-SQL)

L'exemple suivant extrait les noms des paramètres (et les objets auxquels ils appartiennent) définis sur un type défini par l'utilisateur 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

L'exemple suivant extrait des métadonnées limitées pour les utilisateurs détenant des privilèges minimaux sur les paramètres définis sur le type défini par l'utilisateur ComplexNumber.

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

Voir aussi

Concepts

Utilisation de types CLR définis par l'utilisateur

Autres ressources

Exemples de programmabilité CLR

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications