Gestire le proprietà estese via T-SQL

Sql Server permette di documentare praticamente tutti gli oggetti contenuti in un database sfruttando le proprietà estese (o exteded properties). Lo standard scelto da Microsoft prevede di chiamare questa proprietà estesa 'MS_Description'.

Sulla piazza esistono poi vari tool, alcuni anche dal costo abbordabile, che permettono di generare documentazione dall’aspetto professionale proprio a partire da queste proprietà: per comiciare, questo articolo (in inglese) su Sql Tips fornisce un ottimo elenco di programmi atti allo scopo. Gli strumenti più evoluti permettono di scegliere o affiancare anche altre proprietà estese oltre a quella di default vista sopra, consentendo, ad esempio, di generare documentazione multi-lingua.

Questa proprietà estesa è accessibile soltanto attraverso una function e tre stored procedure di sistema, che ci isolano dalla proprietà stessa, in un certo senso proteggendola. Secondo me vale la pena cominciare ad analizzare prima i parametri, che sono più o meno sempre quelli, e successivamente la function e le stored procedure

Parametri

[ @name= ]{‘property_name’}

Nome della proprietà da aggiornare. property_name è di tipo sysname e non può essere NULL. Management Studio, ad esempio, usa la proprietà estesa di nome 'MS_Description'.

[ @value= ]{ ‘value’}

Valore associato alla proprietà. value è di tipo sql_variant e il valore predefinito è NULL. La dimensione di @value non può superare 7.500 byte.
Questo è l’unico parametro che non viene usato dalla function, ma solo dalle stored procedure.

[ @level0type= ]{ ‘level0_object_type’}

Utente o tipo definito dall’utente. level0_object_type è di tipo varchar(128) e il valore predefinito è NULL, nel qual caso ci si riferisce al database in uso.
I possibili valori sono ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE e NULL.

Importante

I tipi USER e TYPE come tipi di livello 0 verranno rimossi in una versione futura di SQL Server, perciò meglio evitare di usarli, sostituendo:
– USER con il solo SCHEMA come @level0type
– TYPE con SCHEMA come @level0type e TYPE come @leveltype.

[ @level0name= ]{ ‘level0_object_name’}

Nome del tipo di oggetto di livello 1 specificato. level0name è di tipo sysname e il valore predefinito è NULL (ammesso però solo se @level0type = NULL).

[ @level1type= ]{ ‘level1_object_type’}

Tipo di oggetto di livello 1. level1_object_type è di tipo varchar(128) e il valore predefinito è NULL.
I possibili valori sono AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TYPE, VIEW, XML SCHEMA COLLECTION e NULL.

[ @level1name= ]{ ‘level1_object_name’}

Nome del tipo di oggetto di livello 1 specificato. level1_object_name è di tipo sysname e il valore predefinito è NULL; nel quale caso ci si riferisce all’oggetto di tipo level0type e nome level0name.

[ @level2type= ]{ ‘level2_object_type’}

Tipo di oggetto di livello 2. level2_object_type è di tipo varchar(128) e il valore predefinito è NULL, accettabile però solo se anche level1type =  NULL.
I possibili valori sono COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER e NULL.

[ @level2name= ]{ ‘level2_object_name’}

Nome del tipo di oggetto di livello 2 specificato. level2_object_name è di tipo sysname e il valore predefinito è NULL.

sp_AddExtendedProperty

Inserisce la proprietà estesa per l’oggetto indicato tramite i parametri; prerequisito fondamentale è che quell’oggetto non presenti già la proprietà valorizzata (nemmeno vuota) altrimenti verrà restituito un errore.

fn_ListExtendedProperty

Restituisce l’elenco delle eventuali proprietà estese corrispondenti ai parametri passati; ad esempio:

Si noti che questo la funzione ammette che la variabile @propertyName sia lasciata a NULL: in questo caso verranno restituite tutte le proprietà estese definite su quell’oggetto.

sp_UpdateExtendedProperty

Aggiorna la propretà estesa per l’oggetto indicato; in questo caso il prerequisito fondamentale è che la proprietà estesa per l’oggetto esista già (anche vuota), altrimenti verrà generato un errore.

sp_DropExtendedProperty

Rimuove la proprietà estesa per l’ggetto indicato; anche in questo caso, ovviamente, il prerequisito fondamentale è che la proprietà stessa esista già (anche vuota), altrimenti verrà generato un errore.

La stored procedure DocumentObject

Viste queste premesse, non è difficile mettere in piedi una stored procedure che ci semplifichi la vita mettendoci al riparo da tutte queste complessità. Chiamerò la stored procedure DocumentObject e la metterò nello schema Turro (sì, sono vanitoso!).

Nello specifico, la stored procedure accetterà tutti i parametri discussi sopra ed in base ad essi deciderà come comportarsi:

• se il parametro @value viene passato valorizzato a NULL, allora rimuoverà la proprietà estesa per quell’oggetto
• se il parametro @value ha un valore diverso da NULL e NON esiste per quell’oggetto, la proprietà estesa verrà inserita
• se il parametro @value ha un valore diverso da NULL e esiste per quell’oggetto, la proprietà estesa verrà aggiornata

Per default (ossia lasciandone il valore a NULL) il parametro @propertyName viene impostato a 'MS_Description'.

IF NOT EXISTS ( SELECT * FROM sys.objects WHERE [Object_Id] = Object_Id( '[Turro].[DocumentObject]' ) )
    EXECUTE ( 'CREATE PROCEDURE [Turro].[DocumentObject] AS BEGIN SET NOCOUNT ON; END' );
GO

ALTER PROCEDURE [Turro].[DocumentObject]
(
    @propertyName   sysName         = N'MS_Description'
  , @value          sql_variant     = NULL
  , @level0type     varchar(128)    = NULL
  , @level0name     sysname         = NULL
  , @level1type     varchar(128)    = NULL
  , @level1name     sysname         = NULL
  , @level2type     varchar(128)    = NULL
  , @level2name     sysname         = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sqlStatement   nvarchar(max);

    IF ( @value IS NULL )
        BEGIN
        SET @sqlStatement = 'sys.sp_DropExtendedProperty @name = N''' + @propertyName + ''''
        END

    ELSE
        BEGIN
        IF EXISTS ( SELECT * FROM sys.fn_listextendedproperty ( @propertyName, @level0type, @level0name, @level1type, @level1name, @level2type, @level2name) )
            BEGIN
            SET @sqlStatement = 'sys.sp_UpdateExtendedProperty @name = N''' + @propertyName + ''', @value = N''' + Convert( nvarchar(max), @value ) + ''''
            END

        ELSE
            BEGIN
            SET @sqlStatement = 'sys.sp_AddExtendedProperty @name = N''' + @propertyName + ''', @value = N''' + Convert( nvarchar(max), @value ) + ''''
            END
        END

    SET @sqlStatement = @sqlStatement + Coalesce ( ', @level0type = N''' + @level0type + ''', @level0name = N''' + @level0name + '''', '' )
    SET @sqlStatement = @sqlStatement + Coalesce ( ', @level1type = N''' + @level1type + ''', @level1name = N''' + @level1name + '''', '' )
    SET @sqlStatement = @sqlStatement + Coalesce ( ', @level2type = N''' + @level2type + ''', @level2name = N''' + @level2name + '''', '' )

    RAISERROR( @sqlStatement, 0, 1) WITH NOWAIT;

    EXECUTE ( @sqlStatement ) ;

    SET NOCOUNT OFF;
END
GO

Gli esempi di uso sono molto simili a quelli visto descrivendo la funzione e le stored procedure di sistema:

-- Scrive la propretà estesa 'MS_Description' per il database in uso
EXECUTE [Turro].[DocumentObject] @value = 'Database to store application data';
GO

-- Scrive la propretà estesa 'MS_Description' per lo schema [Turro] nel database in uso
EXECUTE [Turro].[DocumentObject] @value = 'Schema to group generic objects in the Turro environment.'
                               , @level0type = N'SCHEMA', @level0name = N'Turro';
GO

-- Scrive la propretà estesa 'MS_Description' per la stored procedure [Turro].[DocumentObject] nel database in uso
EXECUTE [Turro].[DocumentObject] @value = 'Stored procedure to document (almost) any object in the database.'
                               , @level0type = N'SCHEMA', @level0name = N'Turro'
                               , @level1Type = N'PROCEDURE', @level1Name = N'DocumentObject';

-- Scrive la propretà estesa 'MS_Description' per il parametro @propertyName della stored procedure [Turro].[DocumentObject] nel database in uso
EXECUTE [Turro].[DocumentObject] @value = 'The name of the extended property to insert/update/delete: if left to NULL (which is default) is set to N''''MS_Description''''.'
                               , @level0type = N'SCHEMA', @level0name = N'Turro'
                               , @level1Type = N'PROCEDURE', @level1Name = N'DocumentObject'
                               , @level2Type = N'PARAMETER', @level2Name = N'@propertyName';