Mögliche Dokumentation für Objekte im Report anzeigen

In diesem Beitrag möchte ich drei wesentliche Aspekte näher erläutern: Erstens eine Methode zur Dokumentation von Objekten im Microsoft SQL Server, zweitens eine vertiefte Betrachtung der erweiterten Eigenschaften (Extended Properties) und drittens die Darstellung dieser Informationen mittels einer gespeicherten Prozedur in einem SSRS-Bericht (SQL Server Reporting Services). Obwohl ich letztes Jahr bereits ausführlich auf die erweiterten Eigenschaften eingegangen bin, werde ich hier an dieser Stelle nur ein kurzes Beispiel präsentieren.

Extended Properties nutzen für verschiedene Aufgaben

Erweiterte Eigenschaften (Extended Properties) bei Objekten in Microsoft SQL Server ermöglichen es, benutzerdefinierte Metadaten zu Datenbankobjekten hinzuzufügen. Diese Metadaten können dazu verwendet werden, zusätzliche Informationen, wie Kommentare, Beschreibungen, oder benutzerdefinierte Attribute, zu speichern.

Erweiterte Eigenschaften sind Metadaten, die einem Datenbankobjekt (wie Tabellen, Spalten, Schemata, Prozeduren, etc.) hinzugefügt werden können. Diese Metadaten werden als Schlüssel-Wert-Paare gespeichert, wobei der Schlüssel der Name der Eigenschaft und der Wert die dazugehörige Information ist. Weiteres siehe in unserem Artikel Extended Propertie

Dokumentation von Objekten im Microsoft SQL Server

Die Dokumentation von Datenbankobjekten ist essenziell für die Verwaltung und Wartung von Datenbanken. Eine strukturierte und umfassende Dokumentation erleichtert nicht nur das Verständnis der Datenbankarchitektur, sondern auch die Zusammenarbeit im Team. Im Microsoft SQL Server können erweiterte Eigenschaften (Extended Properties) verwendet werden, um benutzerdefinierte Metadaten zu Datenbankobjekten hinzuzufügen. Diese Metadaten können Beschreibungen, Kommentare oder andere wichtige Informationen enthalten.

Verwendung von sp_addextendedproperty

Die gespeicherte Prozedur sp_addextendedproperty wird verwendet, um eine erweiterte Eigenschaft zu einem Datenbankobjekt hinzuzufügen. Hier ist die Syntax:

sp_addextendedproperty 
    [ @name = ] 'property_name', 
    [ @value = ] 'property_value' 
    [ , [ @level0type = ] 'level0_object_type' 
    , [ @level0name = ] 'level0_object_name' ] 
    [ , [ @level1type = ] 'level1_object_type' 
    , [ @level1name = ] 'level1_object_name' ] 
    [ , [ @level2type = ] 'level2_object_type' 
    , [ @level2name = ] 'level2_object_name' ]

Parameter:

• @name: Der Name der erweiterten Eigenschaft.
• @value: Der Wert der erweiterten Eigenschaft.
• @level0type: Der Typ des Objekts auf der obersten Ebene (z.B. SCHEMA, TABLE, DATABASE).
• @level0name: Der Name des Objekts auf der obersten Ebene.
• @level1type: Der Typ des Objekts auf der mittleren Ebene (z.B. TABLE, VIEW).
• @level1name: Der Name des Objekts auf der mittleren Ebene.
• @level2type: Der Typ des Objekts auf der untersten Ebene (z.B. COLUMN, INDEX).
• @level2name: Der Name des Objekts auf der untersten Ebene.

Beispiel:
Hinzufügen einer erweiterten Eigenschaft zu einer Tabelle:

EXEC sp_addextendedproperty 
    @name = N'Description', 
    @value = N'This table stores customer information.', 
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE',  @level1name = N'Customers';

Andere nützliche gespeicherte Prozeduren:

1. sp_updateextendedproperty: Aktualisiert den Wert einer vorhandenen erweiterten Eigenschaft.

EXEC sp_updateextendedproperty 
    @name = N'Description', 
    @value = N'Updated description.',
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE',  @level1name = N'Customers';

sp_dropextendedproperty: Entfernt eine erweiterte Eigenschaft.

EXEC sp_dropextendedproperty 
    @name = N'Description',
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE',  @level1name = N'Customers';

fn_listextendedproperty: Eine Funktion, die die erweiterten Eigenschaften eines Objekts auflistet.

SELECT * 
FROM fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', 'Customers', NULL, NULL);

Anwendungsfälle:

• Dokumentation: Erstellen von Metadaten zu Objekten für die interne Dokumentation. Beispielsweise können Beschreibungen von Tabellen und Spalten hinzugefügt werden.

Beispiel: hier Dokumentieren wir unsere[DWH_Stage] Datenbank in Form vonn HTML um dies nachher besser im Report darstellen zu können.

EXEC [DWH_Stage].sys.sp_addextendedproperty @name=N'documentation', @value=N'

<h3>Beschreibung der Stage-Datenbank <code>DWH_Stage</code></h3>
<p>Die Stage-Datenbank <code>DWH_Stage</code> spielt eine entscheidende Rolle im Data Warehouse (DWH)-Prozess. Sie dient als temporärer Speicherort für Daten, die aus verschiedenen Quellsystemen extrahiert werden, bevor sie in das eigentliche Data Warehouse geladen und weiterverarbeitet werden. Diese Staging-Datenbank wird hauptsächlich für folgende Zwecke verwendet:</p>
<h2>Funktion und Nutzen</h2>
<ul>
    <li><strong>Datenintegration:</strong> Die Stage-Datenbank ermöglicht die Integration von Daten aus unterschiedlichen Quellsystemen. Diese Daten können aus verschiedenen Formaten und Strukturen stammen, die in der Stage-Datenbank konsolidiert werden.</li>
    <li><strong>Datenbereinigung:</strong> In der Staging-Phase werden die Daten aufbereitet, bereinigt und transformiert. Dies umfasst das Entfernen von Duplikaten, das Korrigieren von Fehlern und die Standardisierung von Datentypen.</li>
    <li><strong>Transformation:</strong> Komplexe Transformationen, wie das Zusammenführen von Daten aus verschiedenen Quellen oder die Berechnung von abgeleiteten Werten, werden in der Stage-Datenbank durchgeführt, bevor die Daten in das Data Warehouse geladen werden.</li>
    <li><strong>Performance:</strong> Durch die Verwendung einer separaten Stage-Datenbank wird die Performance des Data Warehouse verbessert, da zeitaufwendige Datenverarbeitungsaufgaben ausgelagert werden und das Data Warehouse selbst für Abfragen und Analysen optimiert bleibt.</li>
    <li><strong>Datensicherung:</strong> Die Stage-Datenbank dient als Zwischenspeicher und ermöglicht es, die ursprünglichen Daten vor dem Laden in das Data Warehouse zu sichern. Dies ist besonders wichtig für die Fehlerbehebung und Datenvalidierung.</li>
</ul>
<p>Die Stage-Datenbank <code>DWH_Stage</code> ist somit ein integraler Bestandteil des Data Warehouse-Prozesses, der sicherstellt, dass die Daten konsistent, sauber und für die Analyse im Data Warehouse bereit sind.</p>'
 

• Datenvalidierung: Speicherung benutzerdefinierter Regeln oder Hinweise für die Datenvalidierung.

Datenvalidierung mit erweiterten Eigenschaften (Extended Properties)

Erweiterte Eigenschaften (Extended Properties) können verwendet werden, um Datenvalidierungsregeln direkt in der Datenbank zu dokumentieren. Diese Regeln können dann von Anwendungen oder Skripten genutzt werden, um sicherzustellen, dass die Daten den festgelegten Kriterien entsprechen, bevor sie in die Datenbank eingefügt oder aktualisiert werden.

Beispiel: Dokumentation von Datenvalidierungsregeln

Angenommen, wir haben eine Tabelle Customers mit einer Spalte EmailAddress. Wir möchten sicherstellen, dass die E-Mail-Adressen einem bestimmten Format entsprechen. Wir können die Validierungsregel in einer erweiterten Eigenschaft dokumentieren.

1. Hinzufügen der erweiterten Eigenschaft zur Spalte EmailAddress in der Tabelle Customers:

EXEC sp_addextendedproperty 
    @name = N'ValidationRule', 
    @value = N'The email address must be in a valid format (e.g., user@example.com).', 
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE',  @level1name = N'Customers', 
    @level2type = N'COLUMN', @level2name = N'EmailAddress';

Abrufen und Verwenden der erweiterten Eigenschaft für Datenvalidierung:

Anwendungen oder Skripte können diese Validierungsregel abrufen und verwenden, um Daten zu validieren, bevor sie in die Datenbank eingefügt werden.

SELECT 
    obj.name AS TableName, 
    col.name AS ColumnName, 
    ep.value AS ValidationRule
FROM 
    sys.extended_properties AS ep
JOIN 
    sys.columns AS col ON ep.major_id = col.object_id AND ep.minor_id = col.column_id
JOIN 
    sys.tables AS obj ON col.object_id = obj.object_id
WHERE 
    ep.name = 'ValidationRule';

Beispiel zur Verwendung in einer Anwendung (Pseudo-Code):

In einer Anwendung könnte dieser Code verwendet werden, um Daten zu validieren, bevor sie in die Datenbank eingefügt werden.

function validateEmailAddress(email) {
    // Abrufen der Validierungsregel aus der Datenbank
    validationRule = getValidationRule('dbo', 'Customers', 'EmailAddress');
    
    // Überprüfen, ob die E-Mail-Adresse das richtige Format hat
    if (regexMatch(email, validationRule)) {
        return true;
    } else {
        throw new ValidationError("Invalid email format: " + email);
    }
}

function getValidationRule(schema, table, column) {
    // SQL-Abfrage, um die erweiterte Eigenschaft abzurufen
    query = `
        SELECT ep.value 
        FROM sys.extended_properties AS ep
        JOIN sys.columns AS col ON ep.major_id = col.object_id AND ep.minor_id = col.column_id
        JOIN sys.tables AS obj ON col.object_id = obj.object_id
        WHERE ep.name = 'ValidationRule' 
          AND obj.name = '${table}' 
          AND col.name = '${column}' 
          AND obj.schema_id = SCHEMA_ID('${schema}');
    `;
    result = executeSQL(query);
    return result[0].value;
}

Erweiterte Eigenschaften bieten eine flexible Möglichkeit, zusätzliche Informationen zu Datenbankobjekten zu speichern und zu verwalten, was zu einer besseren Organisation und Nutzung der Datenbank beitragen kann.

Kommen wir zum dritten und letzten Punkt in diesem Artikel:

Eine Prozedur zur Anzeige der erweiterten Eigenschaften.

Warum sollte man eigentlich eine Abfrage für erweiterte Eigenschaften in einer Prozedur auslagern? Dafür gibt es mehrere gute Gründe:

1. Sicherheit

Gespeicherte Prozeduren bieten eine zusätzliche Sicherheitsebene, da die Berechtigungen für die Prozedur unabhängig von den Berechtigungen für die zugrunde liegenden Tabellen und Ansichten verwaltet werden können. Dies bedeutet, dass Benutzer berechtigt sein können, die Prozedur auszuführen, ohne direkten Zugriff auf die zugrunde liegenden Datenbanken oder Tabellen zu haben. In meinem Fall hat der ReportUser nicht die Berechtigung, direkt auf diese Eigenschaften zuzugreifen. Durch die Verwendung einer gespeicherten Prozedur kann ich sicherstellen, dass nur die benötigten Daten bereitgestellt werden, ohne die Sicherheitsrichtlinien zu verletzen.

2. Flexibilität bei der Datenbankauswahl

Da wir explizit über USE [Datenbankname] die Datenbank angeben müssen, ist der einfachste und logischste Weg, dies über eine Prozedur zu erledigen. Die Prozedur ermöglicht es, dynamisch den Kontext zu wechseln und die gewünschten Informationen abzurufen, ohne die Notwendigkeit, komplexe dynamische SQL-Anweisungen in den Berichten selbst zu verwalten.

Weitere Vorteile

Zusätzlich zu den oben genannten Gründen gibt es weitere Vorteile, die die Verwendung einer gespeicherten Prozedur attraktiv machen:

• Wiederverwendbarkeit: Eine gespeicherte Prozedur kann mehrfach aufgerufen und in verschiedenen Berichten oder Anwendungen wiederverwendet werden. Dies spart Entwicklungszeit und reduziert die Fehleranfälligkeit.
• Leistungsoptimierung: Gespeicherte Prozeduren werden auf dem SQL Server kompiliert und optimiert, was zu einer verbesserten Leistung führen kann. Der SQL Server erstellt und speichert einen Ausführungsplan für die Prozedur, was die Ausführung beschleunigen kann.
• Wartbarkeit: Änderungen an der Logik oder den Abfragen müssen nur einmal in der gespeicherten Prozedur vorgenommen werden, anstatt in jedem Bericht oder jeder Anwendung, die die Abfrage verwendet.

Beispiel einer gespeicherten Prozedur

Hier ist ein Beispiel, wie eine gespeicherte Prozedur zur Abfrage von erweiterten Eigenschaften aussehen könnte:

USE [META]
GO

/****** Object:  StoredProcedure [dbo].[pGetDocuPropertiesForDB]    Script Date: 21.06.2024 17:20:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[pGetDocuPropertiesForDB]
    @DBName NVARCHAR(128)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX);

    SET @SQL = N'
USE [' + @DBName + N'];

SELECT
    EP.name AS PropertyName,
    EP.value AS PropertyValue
FROM
    fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL) AS EP
WHERE
    EP.name = ''documentation'';
';

    EXEC sp_executesql @SQL;
END;
GO

EXEC sys.sp_addextendedproperty @name=N'documentation', @value=N'
<h3>Beschreibung der gespeicherten Prozedur pGetDocuPropertiesForDB</h3>
<p>Die gespeicherte Prozedur <code>pGetDocuPropertiesForDB</code> wurde entwickelt, um Dokumentationseigenschaften aus den erweiterten Eigenschaften einer angegebenen Datenbank zu extrahieren. Sie ist darauf spezialisiert, alle für Dokumentationszwecke markierten Einträge zu sammeln.</p>
<h2>Funktionsweise</h2>
<p>Die Prozedur nimmt den Namen der Datenbank als Parameter (<code>@DBName</code>) und führt folgende Schritte aus:</p>
<ol>
    <li>Die Prozedur baut ein dynamisches SQL-Statement auf, das speziell darauf ausgelegt ist, in der übergebenen Datenbank ausgeführt zu werden.</li>
    <li>Das SQL-Statement verwendet die Funktion <code>fn_listextendedproperty</code>, um alle erweiterten Eigenschaften abzufragen, die unter dem Namen 'documentation' gespeichert sind.</li>
    <li>Die Abfrage gibt den Namen und den Wert jeder erweiterten Eigenschaft zurück, wodurch spezifische Dokumentationsinformationen zur Datenbankstruktur bereitgestellt werden.</li>
</ol>
<p>Dieses Vorgehen ermöglicht es, gezielt Informationen zu erfassen, die für die Verwaltung und Dokumentation der Datenbankarchitektur entscheidend sind. Durch die Verwendung von dynamischem SQL kann die Prozedur flexibel in verschiedenen Datenbanken innerhalb einer Serverumgebung eingesetzt werden.</p>
', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'PROCEDURE', @level1name=N'pGetDocuPropertiesForDB'
GO

Verwendung der Prozedur in SSRS

1. 1. Datenquelle konfigurieren:
      • Fügen Sie die Datenquelle hinzu, die auf Ihre SQL Server-Datenbank zeigt.
   2. Datensatz erstellen:
      • Erstellen Sie einen neuen Datensatz, der die gespeicherte Prozedur aufruft.
      • Geben Sie die Prozedur mit den erforderlichen Parametern an, z. B.:

EXEC pGetDocuPropertiesForDB @DBName = 'DWH_Stage';

1. 1. Berichtsdesign:
      • Verwenden Sie die Ergebnisse des Datensatzes, um Ihre Berichtselemente zu gestalten. Sie können z. B. eine Tabelle hinzufügen, die die erweiterten Eigenschaften für eine bestimmte Tabelle oder Spalte anzeigt.

Erweiterte Eigenschaften bieten eine leistungsstarke und flexible Möglichkeit, Metadaten zu Datenbankobjekten im Microsoft SQL Server hinzuzufügen. Durch die Kombination von erweiterten Eigenschaften mit gespeicherten Prozeduren können diese Metadaten effektiv für Dokumentations- und Berichtszwecke genutzt werden. Dies verbessert nicht nur die Transparenz und Verständlichkeit der Datenbankarchitektur, sondern unterstützt auch die effiziente Verwaltung und Wartung der Datenbank.

Prozess zur manuellen Anlage von Extended Properties in SQL Server ohne SQL-Befehle

Das Hinzufügen von erweiterten Eigenschaften (Extended Properties) zu Datenbankobjekten in SQL Server kann auch ohne SQL-Befehle direkt über das SQL Server Management Studio (SSMS) erfolgen. Hier ist eine Schritt-für-Schritt-Anleitung:

1. Starten von SQL Server Management Studio (SSMS):
   • Öffnen Sie SQL Server Management Studio.
   • Stellen Sie eine Verbindung zu Ihrem SQL Server her.
2. Navigieren zu dem gewünschten Datenbankobjekt:
   • Erweitern Sie den Knoten „Datenbanken“ im Objekt-Explorer.
   • Wählen Sie die entsprechende Datenbank aus und erweitern Sie den Datenbankknoten.
   • Navigieren Sie zu dem Objekt, zu dem Sie eine erweiterte Eigenschaft hinzufügen möchten (z.B. Tabelle, Spalte, Schema, etc.).
3. Öffnen der Eigenschaften des Objekts:
   • Klicken Sie mit der rechten Maustaste auf das gewünschte Objekt (z.B. eine Tabelle).
   • Wählen Sie „Eigenschaften“ aus dem Kontextmenü.
4. Hinzufügen der erweiterten Eigenschaft:
   • Im Eigenschaftenfenster navigieren Sie zum Abschnitt „Erweiterte Eigenschaften“ oder „Extended Properties“.
   • Klicken Sie auf die Schaltfläche „Hinzufügen“ oder „Add“.
   • Geben Sie einen Namen für die erweiterte Eigenschaft in das Feld „Name“ oder „Property Name“ ein (z.B. Description).
   • Geben Sie den Wert der erweiterten Eigenschaft in das Feld „Wert“ oder „Value“ ein (z.B. This table stores customer information.).
5. Speichern der Änderungen:
   • Klicken Sie auf „OK“ oder „Anwenden“, um die Änderungen zu speichern und die erweiterte Eigenschaft hinzuzufügen.

Beispiel für die manuelle Anlage einer erweiterten Eigenschaft für eine Tabelle:

Schritt 1: Öffnen von SSMS und Navigieren zur Datenbank

Schritt 2: Öffnen der Eigenschaften der Tabelle

Schritt 3: Hinzufügen einer erweiterten Eigenschaft

Das manuelle Hinzufügen von erweiterten Eigenschaften über SSMS ist eine benutzerfreundliche Methode, um Metadaten zu Datenbankobjekten hinzuzufügen. Diese Methode erfordert keine Kenntnisse von SQL-Befehlen und kann durch einfache GUI-Interaktionen durchgeführt werden. Dies ist besonders nützlich für Benutzer, die eine visuelle Vorgehensweise bevorzugen oder nur gelegentlich erweiterte Eigenschaften hinzufügen müssen.

Wie sind eure Erfahrungen mit erweiterten Eigenschaften bei Objekten im SQL Server? Wie setzt ihr diese ein und welche tollen Prozeduren sind bei euch in den letzten Jahren dadurch entstanden? Schreibt es gerne in die Kommentare!

Ähnliche Beiträge:

Ermitteln von Abhängigkeiten zwischen Berichtselementen und Datenquellen Erweiterung der Rendering-Methoden in SSRS Medianwert mit einer Prozedur berechnen Datensammlung für Langzeitanalysen Datentypen der meisten DBMS Data Catalog -Der Wert von Daten in der heutigen Wirtschaft