Database Inventory

-- ============================================================================
-- ENHANCED GOVERNANCE-FOCUSED INVENTORY QUERY v2.0
-- ============================================================================
-- Purpose: Comprehensive database inventory with governance, compliance, 
--          security, and operational metadata for enterprise data management
-- 
-- Features:
-- • Automated PII detection and risk assessment
-- • Usage pattern analysis for optimization
-- • Compliance mapping with regulatory requirements
-- • Operational flags for maintenance and security
-- • Data quality indicators and sizing metrics
-- 
-- Target Audience: Data governance teams, DBAs, compliance officers, security
-- Database: AdventureWorks2022 (adaptable to any SQL Server database)
-- ============================================================================

-- ============================================================================
-- SECTION 1: DATABASE CONNECTION AND ENVIRONMENT VERIFICATION
-- ============================================================================
-- Connect to target database (modify as needed)
USE AdventureWorks2022;

-- Verify connection and capture environment information
SELECT
    @@SERVERNAME AS server_name,                       -- SQL Server instance name
    DB_NAME() AS database_name,                        -- Current database name
    @@VERSION AS sql_version,                          -- SQL Server version info
    GETDATE() AS [current_time],                       -- Current timestamp (bracketed to avoid reserved keyword)
    USER_NAME() AS [current_user],                     -- Current database user (bracketed to avoid reserved keyword)
    SYSTEM_USER AS login_name;                         -- Current system login name

-- Display connection confirmation messages
PRINT 'Connected to database: ' + DB_NAME();
PRINT 'Starting governance inventory analysis at: ' + CAST(GETDATE() AS VARCHAR(50));

-- ============================================================================
-- SECTION 2: TEMPORARY TABLE CREATION FOR RESULTS STORAGE
-- ============================================================================
-- Drop existing temp table if it exists (for re-runs)
IF OBJECT_ID('tempdb..#DataGovernanceInventory') IS NOT NULL
    DROP TABLE #DataGovernanceInventory;

-- Create comprehensive temporary table to store inventory results
CREATE TABLE #DataGovernanceInventory (
    -- ========================================================================
    -- CORE IDENTIFICATION FIELDS
    -- ========================================================================
    inventory_id INT IDENTITY(1,1) PRIMARY KEY,        -- Unique identifier for each record
    schema_name NVARCHAR(128) NOT NULL,                -- Database schema name
    table_name NVARCHAR(128) NOT NULL,                 -- Table name within schema
    full_table_name NVARCHAR(257) NOT NULL,            -- Fully qualified table name
    
    -- ========================================================================
    -- STRUCTURAL ANALYSIS METRICS
    -- ========================================================================
    total_columns INT NOT NULL,                        -- Total number of columns
    required_columns INT NOT NULL,                     -- Count of NOT NULL columns
    identity_columns INT NOT NULL,                     -- Count of identity/auto-increment columns
    computed_columns INT NOT NULL,                     -- Count of computed/calculated columns
    
    -- ========================================================================
    -- STORAGE AND SIZE METRICS
    -- ========================================================================
    estimated_rows BIGINT NOT NULL,                    -- Approximate row count
    total_size_mb DECIMAL(10,2) NOT NULL,              -- Total allocated space in MB
    used_size_mb DECIMAL(10,2) NOT NULL,               -- Actually used space in MB
    data_size_mb DECIMAL(10,2) NOT NULL,               -- Pure data size in MB
    
    -- ========================================================================
    -- TEMPORAL METADATA
    -- ========================================================================
    create_date DATETIME NOT NULL,                     -- Table creation timestamp
    modify_date DATETIME NOT NULL,                     -- Last structural modification
    days_since_modified INT,                           -- Age of last modification
    
    -- ========================================================================
    -- USAGE PATTERN ANALYTICS
    -- ========================================================================
    total_read_operations BIGINT,                      -- Total SELECT operations since restart
    total_write_operations BIGINT,                     -- Total DML operations since restart
    days_since_last_access INT,                        -- Days since last read operation
    
    -- ========================================================================
    -- DATA CLASSIFICATION AND QUALITY
    -- ========================================================================
    size_category VARCHAR(20) NOT NULL,                -- Table size classification
    potential_pii_columns INT NOT NULL,                -- Count of suspected PII columns
    pii_risk_level VARCHAR(20) NOT NULL,               -- PII risk assessment
    
    -- ========================================================================
    -- BUSINESS CONTEXT AND PURPOSE
    -- ========================================================================
    business_purpose NVARCHAR(500) NOT NULL,           -- Business function mapping
    data_sensitivity VARCHAR(50) NOT NULL,             -- Data sensitivity classification
    compliance_scope NVARCHAR(500) NOT NULL,           -- Applicable regulations
    
    -- ========================================================================
    -- OPERATIONAL MANAGEMENT
    -- ========================================================================
    operational_flag VARCHAR(100) NOT NULL,            -- Operational recommendations/alerts
    
    -- ========================================================================
    -- EXTENDED GOVERNANCE FIELDS (FOR FUTURE ENHANCEMENT)
    -- ========================================================================
    data_owner NVARCHAR(100) NULL,                    -- Business data owner
    data_steward NVARCHAR(100) NULL,                  -- Technical data steward
    retention_period_years INT NULL,                   -- Data retention requirement in years
    backup_frequency VARCHAR(50) NULL,                -- Required backup frequency
    archive_eligible BIT DEFAULT 0,                   -- Whether table is archival candidate
    
    -- ========================================================================
    -- AUDIT AND TRACKING
    -- ========================================================================
    analysis_timestamp DATETIME DEFAULT GETDATE(),     -- When this analysis was performed
    analysis_user NVARCHAR(128) DEFAULT SYSTEM_USER   -- Who performed the analysis
);

PRINT 'Temporary table #DataGovernanceInventory created successfully';

-- ============================================================================
-- SECTION 3: EXECUTE COMPREHENSIVE INVENTORY QUERY AND POPULATE TABLE
-- ============================================================================
PRINT 'Starting comprehensive data inventory analysis...';

-- ============================================================================
-- CTE 1: TABLE SIZE METRICS
-- ============================================================================
-- Purpose: Pre-calculate accurate storage metrics to avoid complex GROUP BY
-- Benefits: Better performance, cleaner main query, separate data/used/total sizes
WITH TableSizeMetrics AS (
    SELECT 
        t.object_id,
        -- Total allocated space including indexes and unused space
        SUM(a.total_pages) * 8 / 1024.0 AS size_mb,
        -- Actually used space (excludes free space in pages)
        SUM(a.used_pages) * 8 / 1024.0 AS used_size_mb,
        -- Pure data size (excludes indexes and free space)
        SUM(a.data_pages) * 8 / 1024.0 AS data_size_mb
    FROM sys.tables t
    JOIN sys.partitions p ON t.object_id = p.object_id
    JOIN sys.allocation_units a ON p.partition_id = a.container_id
    WHERE p.index_id IN (0,1)  -- Heap tables (0) and clustered indexes (1) only
    GROUP BY t.object_id
),

-- ============================================================================
-- CTE 2: PII DETECTION ENGINE
-- ============================================================================
-- Purpose: Automatically identify potential PII columns based on naming patterns
-- Compliance Impact: Critical for GDPR, CCPA, and other privacy regulations
-- Note: This is heuristic-based; manual review recommended for accuracy
PIIDetection AS (
    SELECT 
        t.object_id,
        -- Count columns that match common PII naming patterns
        COUNT(CASE WHEN c.name LIKE '%ssn%' OR c.name LIKE '%social%'      -- Social Security
                    OR c.name LIKE '%email%' OR c.name LIKE '%phone%'       -- Contact info
                    OR c.name LIKE '%address%' OR c.name LIKE '%birth%'     -- Personal details
                    OR c.name LIKE '%salary%' OR c.name LIKE '%wage%'       -- Financial info
                    OR c.name LIKE '%credit%' OR c.name LIKE '%account%'    -- Account data
                    OR c.name LIKE '%passport%' OR c.name LIKE '%license%'  -- ID documents
              THEN 1 END) AS potential_pii_columns
    FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
    GROUP BY t.object_id
),

-- ============================================================================
-- CTE 3: TABLE USAGE STATISTICS
-- ============================================================================
-- Purpose: Capture table access patterns for operational optimization
-- Benefits: Identify unused tables, performance bottlenecks, archival candidates
-- Note: Statistics reset on SQL Server restart
TableStats AS (
    SELECT 
        t.object_id,
        -- Last access timestamps for different operation types
        s.last_user_seek,    -- Last index seek operation
        s.last_user_scan,    -- Last table/index scan operation  
        s.last_user_lookup,  -- Last key lookup operation
        -- Aggregate read operations (seeks + scans + lookups)
        s.user_seeks + s.user_scans + s.user_lookups AS total_reads,
        -- Write operations (INSERT, UPDATE, DELETE)
        s.user_updates
    FROM sys.tables t
    LEFT JOIN sys.dm_db_index_usage_stats s ON t.object_id = s.object_id AND s.index_id IN (0,1)
)

-- ============================================================================
-- INSERT GOVERNANCE INVENTORY RESULTS INTO TEMPORARY TABLE
-- ============================================================================
-- Note: Extended governance fields (data_owner, data_steward, etc.) will be NULL initially
-- Note: analysis_timestamp and analysis_user fields will auto-populate with DEFAULT values
INSERT INTO #DataGovernanceInventory (
    -- Core identification fields
    schema_name, table_name, full_table_name,
    -- Structural analysis metrics
    total_columns, required_columns, identity_columns, computed_columns,
    -- Storage and size metrics
    estimated_rows, total_size_mb, used_size_mb, data_size_mb,
    -- Temporal metadata
    create_date, modify_date, days_since_modified,
    -- Usage pattern analytics
    total_read_operations, total_write_operations, days_since_last_access,
    -- Data classification and quality
    size_category, potential_pii_columns, pii_risk_level,
    -- Business context and governance
    business_purpose, data_sensitivity, compliance_scope, operational_flag
)

-- ============================================================================
-- MAIN QUERY: COMPREHENSIVE INVENTORY WITH GOVERNANCE METADATA
-- ============================================================================
SELECT
    -- ========================================================================
    -- SECTION 1: CORE TABLE IDENTIFICATION
    -- ========================================================================
    s.name AS schema_name,                              -- Schema containing the table
    t.name AS table_name,                               -- Table name within schema
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS full_table_name,  -- Fully qualified name for referencing
    
    -- ========================================================================
    -- SECTION 2: ENHANCED COLUMN ANALYSIS
    -- ========================================================================
    -- Provides detailed breakdown of column types for data modeling insights
    COUNT(c.column_id) AS total_columns,                -- Total number of columns in the table
    SUM(CASE WHEN c.is_nullable = 0 THEN 1 ELSE 0 END) AS required_columns,      -- Count of NOT NULL columns (data quality indicator)
    SUM(CASE WHEN c.is_identity = 1 THEN 1 ELSE 0 END) AS identity_columns,      -- Count of auto-increment/identity columns
    SUM(CASE WHEN c.is_computed = 1 THEN 1 ELSE 0 END) AS computed_columns,      -- Count of calculated/computed columns
    
    -- ========================================================================
    -- SECTION 3: STORAGE METRICS (ENHANCED ACCURACY)
    -- ========================================================================
    -- More accurate storage calculations using pre-computed CTE results
    COALESCE(p.rows, 0) AS estimated_rows,              -- Estimated row count (may not be exact, updated by statistics)
    COALESCE(tsm.size_mb, 0) AS total_size_mb,          -- Total allocated space in MB (includes indexes and free space)
    COALESCE(tsm.used_size_mb, 0) AS used_size_mb,      -- Actually used space in MB (excludes free space in pages)
    COALESCE(tsm.data_size_mb, 0) AS data_size_mb,      -- Pure data size in MB (excludes indexes and free space)
    
    -- ========================================================================
    -- SECTION 4: TEMPORAL METADATA FOR CHANGE TRACKING
    -- ========================================================================
    t.create_date,                                      -- When the table was originally created
    t.modify_date,                                      -- Last time the table structure was modified
    DATEDIFF(day, t.modify_date, GETDATE()) AS days_since_modified,  -- Age of last structural change
    
    -- ========================================================================
    -- SECTION 5: USAGE PATTERNS FOR OPERATIONAL INSIGHTS
    -- ========================================================================
    -- Critical for identifying unused tables and optimization opportunities
    -- Note: Statistics reset on SQL Server restart, so interpret with caution for new servers
    COALESCE(ts.total_reads, 0) AS total_read_operations,        -- Total SELECT operations since last restart
    COALESCE(ts.user_updates, 0) AS total_write_operations,      -- Total INSERT/UPDATE/DELETE operations
    
    -- Calculate days since last access (any type of read operation)
    CASE 
        WHEN ts.last_user_seek IS NOT NULL OR ts.last_user_scan IS NOT NULL OR ts.last_user_lookup IS NOT NULL
        THEN DATEDIFF(day, COALESCE(ts.last_user_seek, ts.last_user_scan, ts.last_user_lookup), GETDATE())
        ELSE NULL  -- NULL means no recorded access (could be new table or no activity since restart)
    END AS days_since_last_access,
    
    -- ========================================================================
    -- SECTION 6: DATA QUALITY INDICATORS
    -- ========================================================================
    -- Categorize tables by size for different governance approaches
    CASE 
        WHEN COALESCE(p.rows, 0) = 0 THEN 'EMPTY'           -- No data - potential candidate for removal
        WHEN COALESCE(p.rows, 0) < 1000 THEN 'SMALL'        -- Reference/lookup tables, low impact
        WHEN COALESCE(p.rows, 0) < 100000 THEN 'MEDIUM'     -- Standard operational tables  
        WHEN COALESCE(p.rows, 0) < 1000000 THEN 'LARGE'     -- High-volume transactional tables
        ELSE 'VERY_LARGE'                                    -- Enterprise-scale data requiring special attention
    END AS size_category,
    
    -- ========================================================================
    -- SECTION 7: PII RISK ASSESSMENT FOR PRIVACY COMPLIANCE
    -- ========================================================================
    -- Automated PII detection helps prioritize privacy protection efforts
    -- Note: This is heuristic-based detection - manual review recommended for accuracy
    COALESCE(pii.potential_pii_columns, 0) AS potential_pii_columns,    -- Count of columns with PII-like names
    
    -- Risk-based classification for privacy governance prioritization
    CASE 
        WHEN COALESCE(pii.potential_pii_columns, 0) = 0 THEN 'NO_PII_DETECTED'     -- Likely safe from privacy regulations
        WHEN COALESCE(pii.potential_pii_columns, 0) <= 2 THEN 'LOW_PII_RISK'       -- Minimal privacy exposure
        WHEN COALESCE(pii.potential_pii_columns, 0) <= 5 THEN 'MEDIUM_PII_RISK'    -- Moderate privacy exposure
        ELSE 'HIGH_PII_RISK'                                                        -- High privacy exposure - priority for review
    END AS pii_risk_level,
    
    -- ========================================================================
    -- SECTION 8: ENHANCED BUSINESS CONTEXT MAPPING
    -- ========================================================================
    -- Maps technical schemas to business functions for stakeholder communication
    -- This helps non-technical stakeholders understand data purpose and importance
    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'
        -- Enhanced detection for system and operational schemas
        WHEN s.name LIKE '%Log%' OR s.name LIKE '%Audit%' THEN 'Audit and Logging'
        WHEN s.name LIKE '%Config%' OR s.name LIKE '%Setting%' THEN 'System Configuration'
        ELSE 'System/Reference Data'  -- Default for unrecognized schemas
    END AS business_purpose,
    
    -- ========================================================================
    -- SECTION 9: DATA SENSITIVITY CLASSIFICATION (WITH PII AWARENESS)
    -- ========================================================================
    -- Enhanced sensitivity classification that considers both schema context and PII content
    -- This drives security controls, access restrictions, and compliance requirements
    CASE
        -- High sensitivity: Known sensitive schemas OR detected high PII content
        WHEN s.name IN ('Person', 'HumanResources') OR COALESCE(pii.potential_pii_columns, 0) > 3 
        THEN 'HIGH - Personal/Sensitive Data'
        -- Medium sensitivity: Business-critical data OR any PII detected
        WHEN s.name = 'Sales' OR COALESCE(pii.potential_pii_columns, 0) > 0 
        THEN 'MEDIUM - Business Sensitive'
        -- Medium operational: Core business operations data
        WHEN s.name IN ('Production', 'Purchasing') 
        THEN 'MEDIUM - Operational Data'
        -- Low sensitivity: Reference data and system tables
        ELSE 'LOW - Reference Data'
    END AS data_sensitivity,
    
    -- ========================================================================
    -- SECTION 10: EXPANDED COMPLIANCE MAPPING
    -- ========================================================================
    -- Maps data to relevant regulatory frameworks for compliance planning
    -- Helps legal and compliance teams understand regulatory exposure
    CASE
        -- High PII content triggers comprehensive privacy regulations
        WHEN s.name = 'Person' OR COALESCE(pii.potential_pii_columns, 0) > 2
        THEN 'GDPR, CCPA, PIPEDA, PII Protection'
        -- HR data has specific employment law and privacy requirements
        WHEN s.name = 'HumanResources' 
        THEN 'Employment Law, GDPR, CCPA, PII Protection'
        -- Financial data requires SOX compliance and potentially PCI-DSS
        WHEN s.name = 'Sales' 
        THEN 'SOX, Financial Reporting, PCI-DSS (if payment data)'
        -- Audit trails have specific retention and integrity requirements
        WHEN s.name LIKE '%Log%' OR s.name LIKE '%Audit%' 
        THEN 'SOX, Audit Requirements, Data Retention'
        -- Default corporate governance for other data types
        ELSE 'Standard Corporate Governance'
    END AS compliance_scope,
    
    -- ========================================================================
    -- SECTION 11: OPERATIONAL RECOMMENDATIONS ENGINE
    -- ========================================================================
    -- Automated flags to guide operational decisions and risk management
    -- These flags help DBAs and data stewards prioritize their work
    CASE
        -- Flag empty tables that may be obsolete and safe to remove
        WHEN COALESCE(p.rows, 0) = 0 AND DATEDIFF(day, t.create_date, GETDATE()) > 90
        THEN 'REVIEW - Empty table older than 90 days'
        
        -- Flag unused tables that may be candidates for archival to save storage costs
        WHEN COALESCE(ts.total_reads, 0) = 0 AND DATEDIFF(day, t.create_date, GETDATE()) > 180
        THEN 'REVIEW - No reads in 180+ days, consider archival'
        
        -- Security concern: PII detected in unexpected schemas (potential data leakage)
        WHEN COALESCE(pii.potential_pii_columns, 0) > 0 AND s.name NOT IN ('Person', 'HumanResources')
        THEN 'SECURITY - Unexpected PII in non-HR/Person schema'
        
        -- Performance concern: Large tables consuming resources but rarely accessed
        WHEN COALESCE(tsm.size_mb, 0) > 1000 AND COALESCE(ts.total_reads, 0) < 100
        THEN 'PERFORMANCE - Large table with low usage'
        
        -- No issues detected - table appears to be operating normally
        ELSE 'NORMAL'
    END AS operational_flag

-- ============================================================================
-- FROM CLAUSE: SYSTEM CATALOG JOINS FOR COMPREHENSIVE METADATA
-- ============================================================================
FROM sys.schemas s
    -- Core table information - every user table belongs to a schema
    JOIN sys.tables t ON s.schema_id = t.schema_id
    -- Column details for structural analysis - every table has at least one column
    JOIN sys.columns c ON t.object_id = c.object_id
    -- Row count estimates (LEFT JOIN handles edge cases gracefully)
    -- Note: Some tables may not have partition info if they're brand new
    LEFT JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0,1)
    -- Pre-calculated size metrics from CTE (LEFT JOIN handles tables with no allocation units)
    LEFT JOIN TableSizeMetrics tsm ON t.object_id = tsm.object_id
    -- PII detection results from CTE (LEFT JOIN ensures all tables included even if no PII detected)
    LEFT JOIN PIIDetection pii ON t.object_id = pii.object_id
    -- Usage statistics from CTE (LEFT JOIN handles tables with no recorded usage)
    LEFT JOIN TableStats ts ON t.object_id = ts.object_id

-- ============================================================================
-- FILTERS: SCOPE AND DATA QUALITY CONTROLS
-- ============================================================================
WHERE t.type = 'U'  -- User tables only - excludes views ('V'), system tables, functions ('FN', 'IF'), etc.

-- ============================================================================
-- GROUPING: REQUIRED FOR AGGREGATE FUNCTIONS
-- ============================================================================
-- Group by all non-aggregated columns to enable COUNT() and SUM() functions
-- This is required because we're counting columns and summing identity/computed column flags
GROUP BY 
    -- Core identifiers
    s.name, t.name, t.schema_id, t.object_id, 
    -- Partition data (already aggregated at table level)
    p.rows, 
    -- Temporal fields (single values per table)
    t.create_date, t.modify_date,
    -- Pre-calculated metrics from CTEs (already aggregated)
    tsm.size_mb, tsm.used_size_mb, tsm.data_size_mb,
    -- PII detection results (already aggregated)
    pii.potential_pii_columns,
    -- Usage statistics (already aggregated)
    ts.total_reads, ts.user_updates, ts.last_user_seek, ts.last_user_scan, ts.last_user_lookup

-- ============================================================================
-- ORDERING: RISK-BASED PRIORITIZATION FOR GOVERNANCE
-- ============================================================================
-- Sort results to show highest-risk and most important data first
-- This helps governance teams focus on the most critical items
ORDER BY 
    -- Primary sort: PII risk level (highest risk first)
    CASE 
        WHEN COALESCE(pii.potential_pii_columns, 0) > 3 THEN 1  -- High PII risk tables get top priority
        WHEN s.name IN ('Person', 'HumanResources') THEN 2      -- Known sensitive schemas come second
        ELSE 3                                                   -- Other data comes last
    END,
    -- Secondary sort: Alphabetical by schema within priority groups
    s.name,
    -- Tertiary sort: Largest tables first within each schema (size often correlates with importance)
    COALESCE(p.rows, 0) DESC;

-- ============================================================================
-- SECTION 4: RESULTS VERIFICATION AND SUMMARY REPORTING
-- ============================================================================
PRINT 'Governance inventory analysis completed successfully!';

-- Verify insertion success and provide record count (without using variables to avoid batch issues)
SELECT 'Total tables analyzed: ' + CAST(COUNT(*) AS VARCHAR(10)) AS record_count_message
FROM #DataGovernanceInventory;

-- ========================================================================
-- EXECUTIVE SUMMARY: HIGH-LEVEL GOVERNANCE METRICS
-- ========================================================================
-- Provides key metrics for executive reporting and governance dashboards
SELECT 
    'GOVERNANCE INVENTORY SUMMARY - ' + CAST(GETDATE() AS VARCHAR(20)) AS report_section,
    COUNT(*) AS total_tables_analyzed,                           -- Total number of user tables in database
    SUM(estimated_rows) AS total_estimated_rows,                 -- Combined row count across all tables
    CAST(SUM(total_size_mb) AS DECIMAL(10,2)) AS total_database_size_mb,  -- Total storage consumption
    -- Risk indicators for executive attention
    COUNT(CASE WHEN pii_risk_level IN ('HIGH_PII_RISK', 'MEDIUM_PII_RISK') THEN 1 END) AS tables_with_privacy_risk,
    COUNT(CASE WHEN operational_flag <> 'NORMAL' THEN 1 END) AS tables_requiring_attention,
    -- Compliance scope overview
    COUNT(DISTINCT compliance_scope) AS different_compliance_frameworks
FROM #DataGovernanceInventory;

-- ========================================================================
-- DATA SENSITIVITY DISTRIBUTION ANALYSIS
-- ========================================================================
-- Shows how data is distributed across sensitivity levels for resource allocation
SELECT 
    'DATA SENSITIVITY DISTRIBUTION' AS report_section,
    data_sensitivity,
    COUNT(*) AS table_count,                                     -- Number of tables at this sensitivity level
    CAST(SUM(total_size_mb) AS DECIMAL(10,2)) AS total_size_mb, -- Storage consumed by this sensitivity level
    CAST(AVG(estimated_rows) AS BIGINT) AS avg_rows_per_table,   -- Average table size in this category
    -- Calculate percentage of total tables
    CAST(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM #DataGovernanceInventory) AS DECIMAL(5,1)) AS percent_of_tables
FROM #DataGovernanceInventory
GROUP BY data_sensitivity
ORDER BY 
    -- Sort by sensitivity priority for governance focus
    CASE data_sensitivity 
        WHEN 'HIGH - Personal/Sensitive Data' THEN 1
        WHEN 'MEDIUM - Business Sensitive' THEN 2
        WHEN 'MEDIUM - Operational Data' THEN 3
        ELSE 4 
    END;

-- ========================================================================
-- HIGH PRIORITY ITEMS REQUIRING IMMEDIATE GOVERNANCE ACTION
-- ========================================================================
-- Identifies tables that need immediate attention from governance, security, or operations teams
SELECT 
    'HIGH PRIORITY ITEMS FOR IMMEDIATE REVIEW' AS report_section,
    schema_name, 
    table_name, 
    pii_risk_level,                                             -- Privacy risk assessment
    data_sensitivity,                                           -- Business sensitivity classification
    operational_flag,                                           -- Specific operational concern
    estimated_rows,                                             -- Size context for impact assessment
    CAST(total_size_mb AS DECIMAL(10,2)) AS size_mb,          -- Storage impact
    days_since_last_access,                                     -- Usage pattern indicator
    compliance_scope                                            -- Regulatory implications
FROM #DataGovernanceInventory
WHERE 
    -- Include tables with privacy risks
    pii_risk_level IN ('HIGH_PII_RISK', 'MEDIUM_PII_RISK') 
    -- Or tables with operational issues
    OR operational_flag <> 'NORMAL'
ORDER BY 
    -- Prioritize by risk level
    CASE pii_risk_level 
        WHEN 'HIGH_PII_RISK' THEN 1 
        WHEN 'MEDIUM_PII_RISK' THEN 2 
        ELSE 3 
    END,
    -- Then by operational concern
    CASE operational_flag 
        WHEN 'SECURITY - Unexpected PII in non-HR/Person schema' THEN 1
        WHEN 'PERFORMANCE - Large table with low usage' THEN 2
        WHEN 'REVIEW - No reads in 180+ days, consider archival' THEN 3
        ELSE 4
    END,
    -- Finally by size (larger tables get more attention)
    total_size_mb DESC;

-- ========================================================================
-- COMPLETION STATUS AND NEXT STEPS
-- ========================================================================
-- Provides clear indication of completion and guidance for next actions
SELECT 
    'ANALYSIS COMPLETED' AS status,
    'Analysis completed at: ' + CAST(GETDATE() AS VARCHAR(50)) AS completion_timestamp,
    'Results stored in: #DataGovernanceInventory (session scope)' AS temp_table_location,
    'Records available until session ends or server restart' AS data_retention_note,
    'Use additional queries below for deeper analysis' AS next_steps;

-- ============================================================================
-- USAGE NOTES AND RECOMMENDATIONS
-- ============================================================================
-- 1. Run during off-peak hours for best performance on large databases
-- 2. Consider creating indexed views for frequently accessed governance reports
-- 3. Review PII detection results manually - heuristic matching has limitations
-- 4. Usage statistics reset on SQL Server restart - consider baseline measurements
-- 5. For regulatory compliance, supplement with data flow analysis and classification
-- 6. Operational flags provide starting points - always validate before taking action
-- ============================================================================

-- ============================================================================
-- SECTION 5: OPTIONAL ADVANCED ANALYSIS QUERIES
-- ============================================================================
-- These queries provide additional insights for specific governance use cases
-- Uncomment and modify as needed for your specific requirements

/*
-- ========================================================================
-- SCHEMA-LEVEL SUMMARY FOR DEPARTMENTAL REPORTING
-- ========================================================================
-- Useful for understanding data distribution across business areas
-- Helps with resource allocation and departmental data governance planning
SELECT 
    'SCHEMA-LEVEL SUMMARY' AS report_type,
    schema_name,
    COUNT(*) as table_count,                                    -- Number of tables in this schema
    SUM(estimated_rows) as total_rows,                          -- Combined rows across all tables
    CAST(SUM(total_size_mb) AS DECIMAL(10,2)) as total_size_mb, -- Storage consumption by schema
    CAST(AVG(potential_pii_columns) AS DECIMAL(3,1)) as avg_pii_columns_per_table, -- PII density
    -- Calculate percentage of database size consumed by this schema
    CAST(SUM(total_size_mb) * 100.0 / (SELECT SUM(total_size_mb) FROM #DataGovernanceInventory) AS DECIMAL(5,1)) AS percent_of_database_size
FROM #DataGovernanceInventory
GROUP BY schema_name
ORDER BY total_size_mb DESC;

-- ========================================================================
-- COMPLIANCE FRAMEWORK IMPACT ANALYSIS
-- ========================================================================
-- Shows which regulatory frameworks affect how much data
-- Critical for compliance budgeting and risk assessment
SELECT 
    'COMPLIANCE IMPACT ANALYSIS' AS report_type,
    compliance_scope,
    COUNT(*) as affected_tables,                                -- Number of tables under this compliance scope
    CAST(SUM(total_size_mb) AS DECIMAL(10,2)) as total_data_size_mb, -- Amount of data subject to these regulations
    SUM(estimated_rows) as total_affected_rows,                 -- Number of records under regulatory scope
    -- Show percentage of database under this compliance framework
    CAST(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM #DataGovernanceInventory) AS DECIMAL(5,1)) AS percent_of_tables
FROM #DataGovernanceInventory
GROUP BY compliance_scope
ORDER BY affected_tables DESC;

-- ========================================================================
-- OPERATIONAL EFFICIENCY OPPORTUNITIES
-- ========================================================================
-- Identifies specific optimization opportunities with potential impact
-- Helps prioritize operational improvements and cost savings
SELECT 
    'OPERATIONAL OPTIMIZATION OPPORTUNITIES' AS report_type,
    operational_flag,
    COUNT(*) as table_count,
    CAST(SUM(total_size_mb) AS DECIMAL(10,2)) as potential_storage_impact_mb, -- Storage that could be reclaimed
    SUM(estimated_rows) as affected_rows,
    -- Show tables that could be archived or optimized
    STRING_AGG(schema_name + '.' + table_name, ', ') WITHIN GROUP (ORDER BY total_size_mb DESC) as example_tables
FROM #DataGovernanceInventory
WHERE operational_flag <> 'NORMAL'  -- Only show tables with actionable recommendations
GROUP BY operational_flag
ORDER BY potential_storage_impact_mb DESC;

-- ========================================================================
-- PII RISK HEAT MAP BY BUSINESS AREA
-- ========================================================================
-- Cross-tabulates business purpose with PII risk for targeted remediation
-- Helps security teams focus on the highest-risk business areas
SELECT 
    'PII RISK BY BUSINESS AREA' AS report_type,
    business_purpose,
    SUM(CASE WHEN pii_risk_level = 'HIGH_PII_RISK' THEN 1 ELSE 0 END) as high_risk_tables,
    SUM(CASE WHEN pii_risk_level = 'MEDIUM_PII_RISK' THEN 1 ELSE 0 END) as medium_risk_tables,
    SUM(CASE WHEN pii_risk_level = 'LOW_PII_RISK' THEN 1 ELSE 0 END) as low_risk_tables,
    SUM(CASE WHEN pii_risk_level = 'NO_PII_DETECTED' THEN 1 ELSE 0 END) as no_pii_detected,
    COUNT(*) as total_tables_in_area,
    -- Calculate risk percentage for this business area
    CAST((SUM(CASE WHEN pii_risk_level IN ('HIGH_PII_RISK', 'MEDIUM_PII_RISK') THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) AS DECIMAL(5,1)) as pii_risk_percentage
FROM #DataGovernanceInventory
GROUP BY business_purpose
ORDER BY high_risk_tables DESC, medium_risk_tables DESC;

-- ========================================================================
-- DATA LIFECYCLE MANAGEMENT CANDIDATES
-- ========================================================================
-- Identifies tables for archival, retention policy application, or cleanup
-- Supports data lifecycle management and storage cost optimization
SELECT 
    'DATA LIFECYCLE MANAGEMENT CANDIDATES' AS report_type,
    schema_name + '.' + table_name as full_table_name,
    size_category,
    estimated_rows,
    CAST(total_size_mb AS DECIMAL(10,2)) as size_mb,
    days_since_last_access,
    days_since_modified,
    operational_flag,
    -- Suggest lifecycle action based on usage patterns
    CASE 
        WHEN days_since_last_access > 365 AND total_size_mb > 100 THEN 'ARCHIVE - Large unused table'
        WHEN days_since_last_access > 180 AND estimated_rows = 0 THEN 'DELETE - Empty unused table'
        WHEN days_since_modified > 730 AND days_since_last_access > 90 THEN 'ARCHIVE - Stale data'
        WHEN total_size_mb > 1000 AND days_since_last_access > 30 THEN 'REVIEW - Large infrequently used'
        ELSE 'RETAIN - Active or recent'
    END as lifecycle_recommendation
FROM #DataGovernanceInventory
WHERE 
    -- Focus on tables that could benefit from lifecycle management
    days_since_last_access > 30 OR estimated_rows = 0 OR total_size_mb > 500
ORDER BY 
    CASE 
        WHEN days_since_last_access > 365 AND total_size_mb > 100 THEN 1
        WHEN days_since_last_access > 180 AND estimated_rows = 0 THEN 2
        ELSE 3
    END,
    total_size_mb DESC;

-- ========================================================================
-- EXPORT TO PERMANENT TABLE (OPTIONAL)
-- ========================================================================
-- Uncomment and modify the table name to save results permanently
-- Useful for historical tracking and trend analysis over time
-- Note: Modify the table name to include date/time for version control
/*
SELECT 
    GETDATE() as snapshot_date,  -- Add timestamp for historical tracking
    * 
INTO dbo.DataGovernanceInventory_20250806  -- Modify date as needed
FROM #DataGovernanceInventory;

PRINT 'Results exported to permanent table: dbo.DataGovernanceInventory_20250806';
*/

-- ========================================================================
-- CUSTOM ANALYSIS TEMPLATE
-- ========================================================================
-- Template for additional custom analysis based on specific business needs
/*
SELECT 
    'CUSTOM ANALYSIS' AS report_type,
    -- Add your custom fields here
    schema_name,
    table_name,
    business_purpose,
    data_sensitivity,
    -- Add custom calculations or groupings as needed
    COUNT(*) OVER (PARTITION BY schema_name) as tables_in_schema,
    RANK() OVER (ORDER BY total_size_mb DESC) as size_rank
FROM #DataGovernanceInventory
WHERE 
    -- Add your custom filters here
    data_sensitivity LIKE '%HIGH%'  -- Example: focus on high sensitivity data
ORDER BY 
    -- Add your custom sorting here
    total_size_mb DESC;
*/