Tra le nuove funzionalità di SQL Server 2000 vi è la possibilità di aggiungere informazioni personalizzate agli oggetti di un database. Queste vengono comunemente chiamate proprietà estese In questo articolo vedremo come poterle gestire in modo completo via Enterprise Manager o T-SQL.
Un dizionario dati per gli oggetti SQL Server
In SQL Server quando creiamo una tabella ci preoccupiamo esclusivamente di nominare le colonne e scegliere il tipo dati appropriato da associare ad esse.
Con la versione 2000 di SQL Server abbiamo però anche un'altra possibilità: aggiungere un contenuto descrittivo ad ogni colonna. Vediamo l'immagine sottostante:
Le informazioni immesse nel campo "Descrizione" della colonna status in tabella Tabella1, verranno salvate internamente come una "proprietà estesa", cioè come una sorta di metadato collegato alla colonna. Oltre che dalla vista di creazione tabella, in EM possiamo aggiungere contenuti descrittivi alle colonne anche dall'area di lavoro di un diagramma, ecco uno screenshot:
La struttura di una proprietà estesa è la seguente:
- Un attributo (è formato da un nome ed un valore o contenuto dell'attributo)
- Un riferimento all'oggetto del database al quale l'attributo viene legato
Tramite queste informazioni SQL Server riesce e gestire le proprietà estese. Ora vedremo come.
Gestire le proprietà estese con T-SQL
La gestione delle proprietà estese in SQL Server viene fatta tramite delle specifiche procedure di sistema, in dettaglio:
1 - sp_addextendedproperty
Per aggiungere una nuova proprietà estesa ad una colonna di una tabella:
DECLARE @v sql_variant
SET @v = N'È' il campo email'
EXECUTE <a href="http://msdn.microsoft.com/library/en- us/tsqlref/ts_sp_adda_4ld5.asp" target="_blank" title="Link esterno">sp_addextendedproperty</a> N'MS_Description', @v, N'user', N'dbo', N'table', N'Tabella1', N'column',N'email'
Questa procedura accetta i seguenti parametri:
- @name è il nome dell'attributo, cioè della proprietà estesa.
- @value è il valore dell'attributo, cioè il del contenuto della proprietà estesa.
- @level0type è l'utente o il proprietario. Viene usato il valore "user" o "type".
- @level0name Il nome del proprietario o dell'ut ente, tipicamente dbo.
- @level1type rappresenta il tipo di oggetto al livello 1.
Può avere i seguenti valori table, view, procedure, function, default, rule o NULL. - @level1name il nome dell'oggetto al livello 1.
- @level2type rappresenta il tipo di oggetto al livello 2.
Può avere i seguenti valori column, parameter, index, constraint, trigger e NULL. - @level2name Il nome dell'oggetto al livello 2.
Riassumendo possiamo seguire questa gerarchia nell'assegnazione delle proprietà estese agli oggetti del database:
Quindi per aggiungere un contenuto descrittivo ad una tabella dobbiamo passare al livello 1 ed eseguire il codice sottostante (il livello 0 è sempre presente):
EXECUTE <a href="http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_adda_4ld5.asp" target="_blank" title="Link esterno">sp_addextendedproperty</a> N'MS_Description', N'Tabella degli ordini', N'user', N'dbo', N'table', N'T_Order'
Di conseguenza per aggiungere un contenuto descrittivo ad una colonna della stessa tabella dovremo passare al livello 2:
EXECUTE <a href="http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_adda_4ld5.asp" target="_blank">sp_addextendedproperty</a> N'MS_Description', N'ID dell''ordine', N'user', N'dbo', N'table', N'T_Order', N'column', N'ORD_ID'
Se invece volessimo aggiungere un contenuto descrittivo per l'indice LastName della tabella Employees basterebbe scrivere:
EXECUTE <a href="http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_adda_4ld5.asp" target="_blank">sp_addextendedproperty</a> N'MS_Description', N'INDICE Cognome Impiegato', N'user', N'dbo', N'table', N'Employees', N'index', N'LastName'
La logica non cambia anche per le altre stored procedure del gruppo.
Attenzione: se tentiamo di usare la procedura sp_addextendedproperty su un oggetto che già contiene una proprietà estesa l'operazione fallisce.
2 - sp_updateextendedproperty
Per aggiornare il valore di una proprietà estesa già abbinata ad una colonna di una tabella:
DECLARE @v sql_variant
SET @v = N'È' il campo email, non può essere nullo'
EXECUTE <a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_4ld5.asp" target="_blank">sp_updateextendedproperty</a> N'MS_Description', @v, N'user', N'dbo', N'table', N'Tabella1', N'column', N'email'
3 - sp_dropextendedproperty
Per eliminare una proprietà estesa da una colonna di una tabella:
EXECUTE <a href="http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_drop_00q1.asp" target="_blank">sp_dropextendedproperty</a>
'Descrizione','user', dbo, 'table', N'Tabella1', N'column', N'email'
Estrarre le descrizioni abbinate alle colonne della tabella
Se inserire le descrizioni per le colonne è piuttosto semplice, visualizzarle
non è così intuitivo. SQL Server salva queste informazioni su tabelle di sistema
nascoste agli utenti per cui è necessario effettuare una query "particolare" per
recuperare questo genere di dati. Vediamo un esempio:
Use tempdb
GO
select o.[name] as 'Nome tabella', c.[name] as 'Nome colonna', e.value as 'Descrizione abbinata alla colonna'
from sysobjects o inner join syscolumns c on o.id = c.id left join
::<a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_7l7t.asp" target="_blank">FN_LISTEXTENDEDPROPERTY</a>(null,N'user',N'dbo',N'table', N'Tabella1', N'column', null) e on c.name = e.objname
where o.name = @nome_tabella
order by c.colorder
La query utilizza la funzione di sistema ::FN_LISTEXTENDEDPROPERTY. Essa restituisce una tabella virtuale contente tutte le proprietà estese associate all'oggetto localizzato tramite i parametri di chiamata.
::FN_LISTEXTENDEDPROPERTY accetta in ingresso tutti i parametri della stored procedure sp_addextendedproperty, eccetto @value, ed inoltre segue la stessa logica di funzionamento.
Ricordiamoci che tutti i valori immessi nei campi descrittivi via EM, vengono salvati all'interno di una proprietà estesa dal nome "MS_Description".
Posso aggiungere più proprietà estese ad un oggetto del database?
Nell'esempio sottostante vediamo come gestire in modo completo il mantenimento di due proprietà Descrizione e Utilizzo per la tabella Tabella1:
Aggiungo la proprietà estesa "Mia_Descrizione"
DECLARE @v sql_variant
SET @v = N'È' la tabella di prova per le proprietà estesa'
EXECUTE sp_addextendedproperty N'Mia_descrizione', @v, N'user', N'dbo', N'table', N'Tabella1'
Aggiorno il valore della proprietà estesa "Mia_Descrizione"
SET @v = N'È' la tabella di prova per le proprietà estesa, modificata'
EXECUTE sp_updateextendedproperty N'Mia_descrizione', @v, N'user', N'dbo', N'table', N'Tabella1'
Visualizzo il contenuto della proprietà estesa "Mia_Descrizione"
SELECT * FROM ::FN_LISTEXTENDEDPROPERTY(null,N'user',N'dbo',N'table',N'Tabella1', null, null)
--In QA
TABLE Tabella1 Descrizione È la tabella di prova per le proprietà estesa
Aggiungo una ulteriore proprietà estesa dal nome "Utilizzo"
SET @v = N'È' usata solo per motivi di prova'
EXECUTE sp_addextendedproperty N'Utilizzo', @v, N'user', N'dbo', N'table', N'Tabella1'
Visualizzo il contenuto di entrambe le proprietà estese "Mia_Descrizione" e "Utilizzo":
SELECT * FROM ::FN_LISTEXTENDEDPROPERTY(null,N'user',N'dbo',N'table',N'Tabella1',null, null)
--Come risultato in QA avremo:
TABLE Tabella1 Descrizione È la tabella di prova per le proprietà estesa
TABLE Tabella1 Utilizzo È usata solo per motivi di prova
Elimino la proprietà estesa "Mia_Descrizione" e "Utilizzo"
EXECUTE sp_dropextendedproperty 'Descrizione','user', dbo, 'table',N'Tabella1'
EXECUTE sp_dropextendedproperty 'Utilizzo','user', dbo, 'table', N'Tabella1'
Oltre al linguaggio T-SQL possiamo utilizzare anche EM per aggiungere contenuti descrittivi ad una tabella. In questo caso dobbiamo creare un diagramma (se non ne abbiamo già uno), inserire le tabelle che ci interessano e una volta che queste saranno disposte nell'area di lavoro del diagramma procedere come segue:
- fare click con il tasto destro del mouse su una tabella qualsiasi del diagramma
- scegliere la voce "Proprietà" dal menu
- Riempire il campo "Descrizione" della maschera visualizzata
Ecco un esempio:
A differenza del T-SQL qui posso gestire solamente la proprietà "MS_Description"
Conclusione
Aggiungere informazioni personalizzate agli oggetti del database è una pratica molto utile per documentare e descrivere gli oggetti stessi. Queste informazioni inoltre possono essere utilizzate nella creazione di documentazione ad hoc per i progetti sviluppati tramite SQL Server. In allegato troverete il codice T-SQL discusso nell'articolo.
