Für die professionelle Datenbankentwicklung ist eine saubere Dokumentation unabdingbar. Dazu gehört auch die Pflege von Metainformationen über Datenbanken und die darin enthaltenen Objekte. Für die Dokumentation und Speicherung von Metainformationen gibt es verschiedene Ansätze, zu denen auch die Extended Properties („Erweiterte Eigenschaften“) im Microsoft SQL Server gehören. In diesem Beitrag zeigen wir, wie die Properties als Standard-Feature angewendet werden und wie man sie in DeltaMaster ETL aktiviert.
Definition und Grundlagen
Die Extended Properties gibt es bei nahezu allen Objekten innerhalb einer SQL-Server-Datenbank. Zu den wichtigsten Objekten zählen:
- Datenbank,
- Tabelle,
- Index,
- Tabelle Spalte,
- View,
- View Spalte,
- Prozedur und
- Funktion.
Die Properties können über das SQL-Server-Management-Studio (SSMS) angelegt und genutzt werden. Per Rechtsklick auf das entsprechende Objekt können unter „Eigenschaften“ die „Erweiterten Eigenschaften“ zur Dokumentation bearbeitet werden. Jede Property hat einen Namen bzw. eine Klassifikation, für die maximal 125 Zeichen angegeben werden dürfen, und einen Wert, für dessen Bezeichnung ein Limit von 7.500 Zeichen besteht.
Abb. 1: Datenbank mit erweiterten Eigenschaften
Die Properties werden vom SQL-Server als ein hierarchisches Modell mit vier Leveln verwaltet, die der Drill-down-Hierarchie im SSMS entsprechen (vgl. Abbildung 2).
Abb. 2: Hierarchie im Management Studio
Oberster Knoten ist die Datenbank, ohne Level, dann folgen die Level0 bis Level2. Diese Level-Einteilung treffen wir gleich beim Erzeugen und Verwalten der Properties über TSQL wieder.
Verwalten der Extended Properties
Der SQL Server liefert standardmäßig drei wichtige Prozeduren für die tägliche Arbeit mit den Properties aus:
- sp_addextendedproperty
- sp_updateextendedproperty
- sp_dropextendedproperty
Schauen wir uns zunächst die Syntax zur Erstellung einer neuen Property auf Datenbankebene an.
EXECUTE sp_addextendedproperty
@name = N'Technischer AP'
,@value = N'Frau Meyer'Das Ergebnis ist ein neuer Eintrag auf Ebene der Datenbank.
Abb. 3: Neue Property
Die Adressierung der Property erfolgt auf dieser Ebene über den Namen bzw. die Klassifikation und den Wert. Die Syntax ist bei den Prozeduren „sp_updateextendedproperty“ und „sp_dropextendedproperty“ gleich.
Um eine Property auf Ebene einer Tabelle zu referenzieren, sieht der Code folgendermaßen aus:
EXECUTE sp_addextendedproperty
@name = N'Fachliche Beschreibung'
,@value = N'Beinhaltet Stammdaten und Attribute über die Kunden aus dem CRM System'
,@level0type = N'SCHEMA'
,@level0name = 'dbo'
,@level1type = N'Table'
,@level1name = 'T_Import_DIM_Customer'In diesem Beispiel wird eine Property auf Level01 auf Tabellenebene hinzugefügt.
Die Parameter „name“ und „value“ bleiben, es kommen allerdings die Parameter für Level0 und Level1 neu hinzu, jeweils in der Ausprägung „leveltype“ und „levelname“. Inhaltlich ist Level0 immer das Datenbank-Schema und Level1 das Objekt, hier also die Tabelle.
Eine Prozedur befindet sich auf der gleichen hierarchischen Stufe und wird mit level1type „Procedure“ adressiert.
EXECUTE sp_addextendedproperty
@name = N'Fachliche Beschreibung'
,@value = N'Setzt alle Dateneingaben der Planungsrunde zurück'
,@level0type = N'SCHEMA'
,@level0name = 'dbo'
,@level1type = N'Procedure'
,@level1name = 'P_APP_Clear_DataEntries'In diesem Beispiel wird eine Property auf Level 01 für eine Prozedur hinzugefügt.
Der unterste Level für die Extended Properties ist Level02, hier eine Tabellenspalte. Sie wird mit folgender Syntax angesprochen:
EXECUTE sp_addextendedproperty
@name = N'Fachliche Beschreibung'
,@value = N'Schlüssel Vertriebskanal aus Sicht der Zentrale'
,@level0type = N'SCHEMA'
,@level0name = 'dbo'
,@level1type = N'Table'
,@level1name = 'T_Import_DIM_Customer'
,@level2type = 'Column'
,@level2name = 'ChannelID';Mit diesem Code wird eine Property auf Level02 hinzugefügt.
In der Online-Dokumentation von Microsoft sind die einzelnen Objekte und die korrekte Syntax exemplarisch beschrieben.
Auswerten der Extended Properties
Die Daten können über die View „sys.extended_properties“ ausgelesen werden.
Abb. 4: System-View „sys.extended_properties“
SELECT
extprop.[class],
extprop.[class_desc],
extprop.[major_id],
extprop.[minor_id],
extprop.[name],
extprop.[value]
FROM
sys.extended_properties extprop
Abb. 5: Ergebnis der Abfrage
Diese Liste kann sehr lang und unstrukturiert werden. Deshalb kann z. B. das Objekt über den „object_id“-Befehl in TSQL an die Abfrage übergeben werden.
SELECT
extprop.[class],
extprop.[class_desc],
extprop.[major_id],
extprop.[minor_id],
extprop.[name],
extprop.[value]
FROM
sys.extended_properties extprop
WHERE
major_id = OBJECT_ID('dbo.T_Import_DIM_Customer');
Abb. 6: Ergebnis der Abfrage mit Parameter
Wer sich mit den System Catalog Views des SQL Server beschäftigt hat, weiß, dass die Verknüpfung zwischen mehreren Objekten weitere Informationen bereitstellt. Wir zeigen hier exemplarisch die Verknüpfung mit „sys.tables“, um den Tabellennamen hinzuzufügen:
SELECT
extprop.[class],
extprop.[class_desc],
extprop.[major_id],
extprop.[minor_id],
tbl.[name],
extprop.[name],
extprop.[value]
FROM
sys.extended_properties extprop
INNER JOIN
sys.tables tbl
ON
extprop.major_id = tbl.object_id
Abb. 7: Ergebnis der Abfrage mit Verknüpfung
Weitere Skripte und Information dazu finden sich in vielen Online-Publikationen.
Standardmäßig bietet der SQL Server auch eine Funktion zum Auslesen der Extended Properties an:
Abb. 8: Parameter der Funktion zum Auslesen der Extended Properties
Bei der Abfrage über die Funktion gibt es die Möglichkeit, mit NULL-Values zu arbeiten:
SELECT *
FROM
fn_listextendedproperty(NULL, 'Schema', 'dbo', 'Table', NULL, NULL, NULL);
SELECT *
FROM
fn_listextendedproperty(NULL, 'Schema', 'dbo', 'Table', 'T_Import_DIM_Customer', 'Column', NULL)
SELECT *
FROM
fn_listextendedproperty(
'Fachliche Beschreibung',
'Schema',
'dbo',
'Table',
'T_Import_DIM_Customer',
'Column',
'ChannelID');
Abb. 9: Abfrage-Ergebnisse mit verschiedenen Parametern
Extended Properties mit DeltaMaster ETL
Auch in DeltaMaster ETL haben wir die Möglichkeit, Extended Properties zur Dokumentation zu nutzen. Diese Option kann im Bericht „Relational Object Parameters“ aktiviert werden.
Abb. 10: Aktivieren der Extended Properties in DeltaMaster ETL
Mit den „Relational Object Parameters“ werden standardmäßig Metainformationen zu den von DeltaMaster ETL erzeugten Objekten hinzugefügt. Dafür werden bei der Aktivierung diese vier Felder mit folgender Bedeutung (laut ETL-Administrator-Referenz) an das Objekt gehängt:
- CreateCSID = ID des „Create rel. schema”-Laufs, mit dem das Objekt angelegt wurde – die IDs entsprechen denen in den entsprechenden History-Berichten
- CreateVersion = DeltaMaster-ETL-Version, mit der das Objekt angelegt wurde
- CSObjType = interne Objekterkennung
- FactID/DimID/… = Eindeutige ID aus der Modelldefinition
Abb. 11: Ergebnis der Option „Relational Object Parameters“ in DeltaMaster ETL
