Utilisation de propriétés étendues sur les objets de base de données

Définition de propriétés étendues

Chaque propriété étendue a un nom et une valeur définis par l'utilisateur. La valeur d'une propriété étendue est une valeur sql_variant qui peut contenir jusqu'à 7 500 octets de données. Vous pouvez ajouter plusieurs propriétés étendues à chaque objet.

Les objets d'une base de données SQL Server sont répartis sur trois niveaux (0, 1 et 2) pour la définition des propriétés étendues. Le niveau 0 est le plus élevé et est défini en tant qu'objets contenus dans l'étendue de la base de données. Les objets de niveau 1 appartiennent à l'étendue d'un utilisateur ou d'un schéma, et ils contiennent les objets de niveau 2. Il est possible de définir des propriétés étendues pour les objets de ces trois niveaux.

Les références à un objet d'un niveau donné doivent être qualifiées par les noms des objets de niveau supérieur possédant ou contenant l'objet en question. Par exemple, lorsque vous ajoutez une propriété étendue à une colonne de table (niveau 2), vous devez également spécifier le nom de la table (niveau 1) qui contient la colonne ainsi que le schéma (niveau 0) qui contient la table.

Dans l'exemple suivant, la valeur de propriété étendue 'Minimum inventory quantity.' est ajoutée à la colonne SafetyStockLevel de la table Product appartenant au schéma Production .

USE AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Minimum inventory quantity.', 
@level0type = N'SCHEMA', @level0name = Production, 
@level1type = N'TABLE',  @level1name = Product,
@level2type = N'COLUMN', @level2name = SafetyStockLevel;
GO

Ajout de propriétés étendues à des objets

Les tableaux ci-dessous répertorient les objets auxquels vous pouvez ajouter des propriétés étendues. Les types d'objets de niveaux 0, 1 et 2 valides sont indiqués, ainsi que les autorisations requises pour ajouter, supprimer ou afficher les propriétés étendues.

Agrégation

Clé asymétrique

Assembly

Certificat

Contrat (Service Broker)

Base de données

Exemple

USE AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'AdventureWorks2008R2 Sample OLTP Database';
GO

Valeur par défaut

Notification d'événement

Nom de fichier ou de groupe de fichiers

Exemple

USE AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Primary filegroup for the AdventureWorks2008R2 sample database.', 
@level0type = N'FILEGROUP', @level0name = [PRIMARY];
GO

USE AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Primary data file for the AdventureWorks2008R2 sample database.', 
@level0type = N'FILEGROUP', @level0name = [PRIMARY],
@level1type = N'Logical File Name', @level1name = AdventureWorks2008R2_Data;
GO

Fonction

Exemple

USE AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Scalar function returning the quantity of inventory for a specified ProductID.', 
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'FUNCTION', @level1name = ufnGetStock;
GO

USE AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Input parameter. Enter a valid ProductID.', 
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'FUNCTION', @level1name = ufnGetStock,
@level2type = N'PARAMETER', @level2name ='@ProductID';
GO

Type du message

Partition, fonction

Schéma de partition

Repère de plan

Dans l'exemple ci-dessous, une propriété étendue est ajoutée au repère de plan TemplateGuide1.

USE AdventureWorks2008R2;
GO
EXECUTE sp_addextendedproperty @name=N'PlanGuideGroup', @value = N'MyTest', 
@level0type = N'Plan Guide', @level0name = 'TemplateGuide1';

Procédure

Niveau	Type du niveau	Autorisations requises pour ajouter ou supprimer une propriété étendue	Autorisations requises pour afficher une propriété étendue	Commentaires
0	SCHEMA ou USER			SCHEMA doit être utilisé au lieu de USER. Voir « Schéma et utilisateur », plus loin dans cette rubrique.
1	PROCÉDURE	ALTER sur procédure OBJECT Remarque Seuls les membres du rôle serveur fixe sysadmin peuvent ajouter des propriétés étendues aux procédures stockées étendues.	N'importe laquelle sur OBJECT	S'applique à ces procédures stockées définies par l'utilisateur : CLR Transact-SQL filtres de réplication. Si un type de niveau 2 n'est pas spécifié, la propriété s'applique à la procédure elle-même.
2	PARAMETER	ALTER sur OBJECT procédure	N'importe laquelle sur OBJECT

File d'attente (Service Broker)

Liaison de service distant (Service Broker)

Itinéraire (Service Broker)

Règle

Schéma ou utilisateur

Niveau	Type du niveau	Autorisations requises pour ajouter ou supprimer une propriété étendue	Autorisations requises pour afficher une propriété étendue	Commentaires
0	SCHEMA ou USER	ALTER sur SCHEMA ou ALTER sur USER	N'importe laquelle sur APPLICATION ROLE ou N'importe laquelle sur ROLE ou N'importe laquelle sur USER	Si un type de niveau 1 n'est pas spécifié, la propriété s'applique au schéma ou à l'utilisateur lui-même. USER peut correspondre à l'un des éléments suivants : Rôle d'application Rôle de base de données Utilisateur SQL Server Groupe Windows Utilisateur Windows USER ne doit être utilisé que si vous ajoutez une propriété étendue à un utilisateur. Important USER en tant que type de niveau 0 défini sur des objets de type de niveau 1 ou 2 sera supprimé dans une version future de SQL Server. Évitez d'utiliser cette fonction dans un nouveau travail de développement et prévoyez la modification des applications qui l'utilisent actuellement. Utilisez à la place SCHEMA en tant que type de niveau 0. Voir « Schéma et utilisateur », plus loin dans cette rubrique.
1	Variable
2	Variable

Exemple

USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_addextendedproperty 
@name = N'MS_Description',
@value = N'Contains objects related to employees and departments.',
@level0type = N'SCHEMA', 
@level0name = HumanResources;

USE AdventureWorks2008R2;
GO
CREATE APPLICATION ROLE Buyers
WITH Password = '987G^bv876sPY)Y5m23';
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'Application Role for the Purchasing Department.',
@level0type = N'USER',
@level0name = N'Buyers';

Service

Clé symétrique

Synonyme

Table

Exemple

USE AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'Street address information for customers, employees, and vendors.', 
@level0type = N'SCHEMA', @level0name = Person, 
@level1type = N'TABLE',  @level1name = Address;
GO

USE AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Nonclustered index on StateProvinceID.', 
@level0type = N'SCHEMA', @level0name = Person, 
@level1type = N'TABLE',  @level1name = Address,
@level2type = N'INDEX',  @level2name = IX_Address_StateProvinceID;
GO

Déclencheur

Type

Type

Vue

Collection de schémas XML

Objets ne prenant pas en charge les propriétés étendues

Vous ne pouvez pas définir des propriétés étendues sur les objets suivants :

• objets d'étendue de base de données non répertoriés dans les tableaux ci-dessus, et notamment les objets de texte intégral ;

• objets en dehors de l'étendue de base de données, tels que les points d'arrêt HTTP ;

• objets non nommés comme les paramètres des fonctions de partition ;

• certificats, clés symétriques et asymétriques et informations d'identification ;

• objets définis par le système comme les tables système, les vues de catalogue et les procédures stockées système.

Schéma et utilisateur

Dans les versions précédentes de SQL Server, les utilisateurs étaient propriétaires des objets de bases de données telles que les tables, les vues et les déclencheurs. Par conséquent, il était permis d'ajouter une propriété étendue à l'un de ces objets et de spécifier un nom d'utilisateur en guise de type du niveau 0. Les objets de base de données font à présent partie de schémas qui sont indépendants des utilisateurs qui sont propriétaires de ces schémas.

Si vous indiquez USER en guise de type du niveau 0 quand vous appliquez une propriété étendue à un objet de base de données, une ambiguïté peut se produire lors de la résolution de noms. Par exemple, supposons que l'utilisateur Mary détienne deux schémas (Mary et MySchema) et que ceux-ci contiennent tous les deux une table nommée MyTable. Si Mary ajoute une propriété étendue à la table MyTable et spécifie @level0type = N'USER', @level0name = Mary, il n'apparaît pas clairement à quelle table la propriété étendue est appliquée. Pour assurer la compatibilité descendante, SQL Server applique la propriété étendue à la table contenue dans le schéma nommé Mary. Pour plus d'informations sur les utilisateurs et les schémas, consultez Séparation du schéma et de l'utilisateur.

Utilisation de masques de saisie ou de contraintes CHECK

Les contraintes CHECK comme les propriétés étendues qui fournissent un masque de saisie peuvent toutes deux être employées pour spécifier le modèle de données attendu pour les colonnes d'une table ou d'une vue. La plupart des sites optent pour l'un ou l'autre de ces deux éléments, sauf dans les cas suivants :

• si les contraintes CHECK étaient provisoirement utilisées jusqu'à ce que tous les programmes manipulant la table soient modifiés pour qu'ils puissent employer des propriétés de masque de saisie ;

• si le site prend également en charge des utilisateurs pouvant mettre à jour les données par le biais d'outils ad hoc qui ne lisent pas les propriétés étendues.

L'avantage du masque de saisie par rapport à la contrainte CHECK est que la logique est appliquée dans les applications. Ces dernières peuvent générer davantage d'erreurs de type information si l'utilisateur fournit des données à un format incorrect. L'inconvénient du masque de saisie est que l'obtention de la propriété passe obligatoirement par un appel distinct de fn_listextendedproperty ou de sys.extended_properties, et que la logique destinée à appliquer le masque doit être ajoutée dans tous les programmes.

Réplication des propriétés étendues

Les propriétés étendues sont répliquées uniquement lors de la synchronisation initiale entre l'Abonné et le serveur de publication. Si vous ajoutez ou modifiez une propriété étendue après la synchronisation initiale, la modification n'est pas répliquée. Pour plus d'informations sur la réplication des objets de base de données, consultez Publication de données et d'objets de base de données.

Utilisation de propriétés étendues dans les applications

Les propriétés étendues ne fournissent qu'un emplacement nommé pour le stockage des données. Toutes les applications doivent être programmées pour interroger la propriété et réaliser l'action appropriée. Par exemple, l'ajout d'une propriété de légende à une colonne ne génère pas la création d'une légende pouvant être affichée par une application. Chaque application doit être programmée pour lire la légende et l'afficher correctement.

Pour ajouter une propriété étendue

• sp_addextendedproperty (Transact-SQL)

Pour mettre à jour une propriété étendue

• sp_updateextendedproperty (Transact-SQL)

Pour supprimer une propriété étendue

• sp_dropextendedproperty (Transact-SQL)

Pour afficher une propriété étendue

• fn_listextendedproperty (Transact-SQL)

• sys.extended_properties (Transact-SQL)

Voir aussi

Référence

Concepts

Autres ressources