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 aNULL
, allora rimuoverà la proprietà estesa per quell’oggetto - se il parametro
@value
ha un valore diverso daNULL
e NON esiste per quell’oggetto, la proprietà estesa verrà inserita - se il parametro
@value
ha un valore diverso daNULL
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';