/*
================================================================================
SQL SERVER TABLE SIZE AND SPACE UTILIZATION ANALYSIS
================================================================================

DESCRIPTION:
    Comprehensive analysis of table sizes and space utilization within a SQL Server database.
    This script provides detailed insights into storage consumption, helping identify:
    • Tables consuming the most storage space
    • Space efficiency and potential optimization opportunities
    • Row counts and storage allocation patterns
    • Unused space that could be reclaimed through maintenance

FEATURES:
    • Per-table storage analysis with schema information
    • Detailed space breakdown (total, used, unused)
    • Multiple unit reporting (KB and MB for flexibility)
    • Row count correlation with storage size
    • Excludes system tables and diagnostic tables
    • Results ordered by storage consumption (largest first)

OUTPUT COLUMNS:
    - TableName: Name of the table
    - SchemaName: Schema containing the table
    - rows: Number of rows in the table (from partition statistics)
    - TotalSpaceKB: Total allocated space in kilobytes
    - TotalSpaceMB: Total allocated space in megabytes (rounded to 2 decimals)
    - UsedSpaceKB: Actually used space in kilobytes
    - UsedSpaceMB: Actually used space in megabytes (rounded to 2 decimals)
    - UnusedSpaceKB: Allocated but unused space in kilobytes
    - UnusedSpaceMB: Allocated but unused space in megabytes (rounded to 2 decimals)

USAGE SCENARIOS:
    • Database capacity planning and growth forecasting
    • Storage optimization and space reclamation projects
    • Performance troubleshooting (large table identification)
    • Archive and purging strategy development
    • Index maintenance priority assessment
    • Storage cost analysis and optimization

TECHNICAL IMPLEMENTATION:
    • Queries system catalog views for accurate space calculations
    • Aggregates data across all indexes and partitions for each table
    • Joins allocation units to get precise storage metrics
    • Filters out system tables and development artifacts
    • Uses 8KB page size for accurate space calculations

DATA SOURCES:
    • sys.tables: Table metadata and properties
    • sys.indexes: Index information for space aggregation
    • sys.partitions: Partition-level row counts and object relationships
    • sys.allocation_units: Physical storage allocation details
    • sys.schemas: Schema ownership and organization

FILTERS APPLIED:
    • Excludes tables with names starting with 'dt%' (development/diagnostic tables)
    • Excludes Microsoft shipped system tables (is_ms_shipped = 0)
    • Excludes system objects (object_id > 255)
    • Only includes user-created tables in user schemas

PERFORMANCE CONSIDERATIONS:
    • Efficient query using system catalog views
    • Minimal performance impact on production systems
    • Results cached by SQL Server's metadata caching
    • Suitable for regular monitoring and reporting

PERMISSIONS REQUIRED:
    • VIEW DEFINITION permission on target database
    • Membership in db_datareader role (recommended)
    • Access to system catalog views (granted by default to most users)

COMPATIBILITY:
    • SQL Server 2005 and later versions
    • Azure SQL Database compatible
    • SQL Server on Linux compatible
    • Works with all SQL Server editions

AUTHOR: Cloud Engineering Team
CREATED: Database administration and monitoring toolkit
UPDATED: Enhanced with comprehensive space analysis and documentation

================================================================================
*/

SELECT 
    t.name AS TableName,                    -- Table name for identification
    s.name AS SchemaName,                   -- Schema name for organization context
    p.rows,                                 -- Row count from partition statistics
    
    -- Total allocated space calculations (pages * 8KB per page)
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    
    -- Actually used space calculations
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    
    -- Unused (allocated but not used) space calculations
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t                            -- Base table metadata
INNER JOIN      
    sys.indexes i ON t.object_id = i.object_id                     -- All indexes on each table
INNER JOIN 
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id  -- Partition information and row counts
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id      -- Physical storage allocation details
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id                     -- Schema information (LEFT JOIN for safety)
WHERE 
    t.name NOT LIKE 'dt%'                   -- Exclude diagnostic/development tables (common naming pattern)
    AND t.is_ms_shipped = 0                -- Exclude Microsoft system tables
    AND i.object_id > 255                  -- Exclude system objects (object_id <= 255 are system objects)
GROUP BY 
    t.name, s.name, p.rows                 -- Group by table, schema, and row count for aggregation
ORDER BY 
    TotalSpaceMB DESC,                     -- Order by largest tables first
    t.name                                 -- Secondary sort by table name for consistency