Database Schema and Structure Analysis

Overview

This workshop represents the foundational step in establishing a comprehensive data governance program. A database inventory is critical for data governance because you cannot govern what you don't know exists.

This activity creates the baseline documentation needed for:

Data Asset Management: Understanding what data assets exist across the organization
Risk Assessment: Identifying potential vulnerabilities and compliance gaps
Ownership Assignment: Establishing clear data stewardship responsibilities
Policy Application: Determining which governance policies apply to specific datasets
Compliance Mapping: Ensuring regulatory requirements are properly addressed

Required Attendees:

Database Administrator (Technical Lead)
Data Governance Officer
Business Process Owners for Each Schema
Information Security Representative
Compliance/Legal Representative (if regulated industry)

The following workshops are designed to give you an overview of how to manually conduct an analysis of your database schemas.

DBeaver

DBeaver Community is a free cross-platform database tool for developers, database administrators, analysts, and everyone working with data. It supports all popular SQL databases like MySQL, MariaDB, PostgreSQL, SQLite, Apache Family, and more.

From the drop-down list, select SQL Server

Enter the connection details:

Setting

Value

Host

localhost / pdc.pentaho.lab

Port

1443

Database / Schema

AdventureWorks2022

Username

Password

StrongPassword123

Show All Schemas

✅ ON

Trust Server Certificate

✅ ON

Click: Test Connection.
Download the database driver.

Successful connection ..!

Expand the Schemas.

Schema Analysis

We're going to create a temporary table - the advantage of this approach is that multiple workshop participants can each create their own #DataInventory table simultaneously without conflicts, since each session gets its own copy.

Let's create a tmp table to hold the results.

-- Connect to AdventureWorks2022 database
USE AdventureWorks2022;

-- Verify connection and database version
SELECT
    @@SERVERNAME AS server_name,
    DB_NAME() AS database_name,
    @@VERSION AS sql_version,
    GETDATE() AS [current_time];
    
-- Create temporary tables for workshop results
CREATE TABLE #DataInventory (
    schema_name NVARCHAR(128),
    table_name NVARCHAR(128),
    column_count INT,
    required_columns INT,
    estimated_rows BIGINT,
    size_mb DECIMAL(10,2),
    last_modified DATETIME,
    business_purpose NVARCHAR(500),
    data_sensitivity NVARCHAR(50),
    -- Additional governance fields
    data_owner NVARCHAR(100),
    data_steward NVARCHAR(100),
    retention_period INT,
    compliance_tags NVARCHAR(200)
);

The provided query captures essential technical metadata, but for comprehensive data governance, consider these additions:

-- ENHANCED GOVERNANCE-FOCUSED INVENTORY QUERY
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    COUNT(c.column_id) AS column_count,
    SUM(CASE WHEN c.is_nullable = 0 THEN 1 ELSE 0 END) AS required_columns,
    p.rows AS estimated_rows,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS size_mb,
    t.create_date,
    t.modify_date,
    
    -- Business context mapping
    CASE
        WHEN s.name = 'Person' THEN 'Customer and Employee Identity Management'
        WHEN s.name = 'Sales' THEN 'Revenue Operations and Customer Transactions'
        WHEN s.name = 'Production' THEN 'Manufacturing and Inventory Management'
        WHEN s.name = 'Purchasing' THEN 'Vendor Relations and Procurement'
        WHEN s.name = 'HumanResources' THEN 'Employee Lifecycle Management'
        ELSE 'System/Reference Data'
    END AS business_purpose,
    
    -- Data sensitivity indicators
    CASE
        WHEN s.name IN ('Person', 'HumanResources') THEN 'HIGH - Personal Data'
        WHEN s.name = 'Sales' THEN 'MEDIUM - Business Sensitive'
        WHEN s.name IN ('Production', 'Purchasing') THEN 'MEDIUM - Operational Data'
        ELSE 'LOW - Reference Data'
    END AS data_sensitivity,
    
    -- Compliance relevance
    CASE
        WHEN s.name = 'Person' THEN 'GDPR, CCPA, PII Protection'
        WHEN s.name = 'HumanResources' THEN 'Employment Law, PII Protection'
        WHEN s.name = 'Sales' THEN 'Financial Reporting, SOX'
        ELSE 'Standard Corporate Governance'
    END AS compliance_scope

FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.partitions p ON t.object_id = p.object_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.type = 'U' AND p.index_id IN (0,1)
GROUP BY s.name, t.name, p.rows, t.create_date, t.modify_date
ORDER BY s.name, p.rows DESC;

Schema Analysis - Results

PreviousData Discovery NextData Classification and Sensitivity Analysis

Last updated 5 days ago

Was this helpful?