SQL : Les propriétés étendues

SQL Server permet d'ajouter des "méta données" dans une base de données : Les "extended properties".
Les propriétés étendues sont des éléments "clés / valeurs" associé à un élément de la base de données.

Ca sert à quoi ?

A qualifier un élément : documentation interne de la bdd
A rendre dynamique des interfaces : on peut imaginer ajouter des données qui permettent de générer l'IHM d'une saisie de table, ou d'avoir des message d'erreurs, de validation personnalisable...


Pour gérer cela trois procédures stockées :

• sp_addextendedproperty
• sp_updateextendedproperty
• sp_dropextendedproperty

Pour dans l'ordre : Ajouter, Modifier, Supprimer des propriétés étendues

les 6 derniers paramètres de ces procédures, permettent d'indiquer où associer l'information.
Level0type = 'SCHEMA'
Level0Name = 'dbo'

Level1Type = 'TABLE'
Level1Name = 'utilisateur'

Level2Type = 'COLUMN'
Level2Name = 'util_nom'

==> Indique la colonne 'util_nom' de la table 'utilisateur' du schéma 'dbo'

Pour associer une propriété à une table : ne pas préciser le 'level2'
Pour associer une propriété à une vue, fonction, procédure ==> changer les infos dans le 'level1' en conséquence !
Pour associer une propriété à la base elle même : ne pas préciser de niveaux !

Comment on retrouve les infos ?

Avec SSMS : Dans la fenêtre propriétés de l'objet concerné il y a un onglet "propriétés étendues" qui les liste aisément.

Pour les interroger, SQL Server fournit une vue sys.fn_listextendedproperty censée remonter une ou plusieurs infos en fonction des filtre passés : j'ai pas réussi à l'utiliser dans les scénarios suivants : donne moi toutes les propriétés étendues, ou donne moi toutes les propriétés de toutes les tables...
... a creuser ?!??

Heureusement en cherchant un peu on trouve que c'est dans la table : sys.extended_properties que sont stockées les informations.
Voici donc 3 requêtes pour retrouver les informations associées à :

• La base de données :

SELECT name, value 
FROM sys.extended_properties
WHERE major_id = 0 AND minor_id = 0 AND class = 0

• Une Table :

SELECT s.name AS [schema_name], o.name AS [table_name] , p.name, p.value
FROM sys.extended_properties p
INNER JOIN sys.tables t ON p.major_id = t.object_id AND p.minor_id = 0 AND p.class = 1
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = 'utilisateur'

• Toutes les colonnes d'une table :

SELECT s.name AS [schema_name], o.name AS [table_name], c.name AS [column_name], p.name, p.value
FROM sys.extended_properties p
INNER JOIN sys.tables t ON p.major_id = t.object_id
INNER JOIN sys.columns c ON c.object_id = t.object_id AND c.column_id = p.minor_id AND p.class = 1
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = 'utilisateur' 

.. reste à adapter en fonction des besoins.

Enjoy

Add : Avec la suppression de l'objet de la base de données (Ex : suppression d'une colonne) les propriétés étendues sont elles aussi supprimées automatiquement (sans bloquer la suppression). Merci SQL Server !