Mahedee Hasan

Mahedee Hasan

I am a Microsoft MVP (11th times), programmer, software developer, software architect, consultant, author, researcher, technical blogger, technical speaker, trainer and learner. I have more than 17 years experience in the field of software design and development.

How to Find Table Sizes in SQL Server: Complete Guide with Multiple Methods

5 minute read

Introduction

Monitoring table sizes is crucial for SQL Server database administration, performance optimization, and capacity planning. Whether you’re investigating storage usage, identifying tables that need archiving, or planning for database growth, understanding how to accurately measure table sizes is essential.

This comprehensive guide covers multiple methods to find table sizes in SQL Server, from traditional stored procedures to modern system view queries, with practical examples and performance considerations.

Why Monitor Table Sizes?

Understanding table sizes helps with:

  • Capacity Planning: Predicting future storage requirements
  • Performance Optimization: Identifying tables that may need indexing or partitioning
  • Maintenance Planning: Determining which tables need regular maintenance
  • Cost Management: Understanding storage costs in cloud environments
  • Troubleshooting: Investigating sudden database growth or performance issues

Method 1: Using sp_spaceused with sp_msForEachTable (Classic Approach)

This is the traditional method using SQL Server’s built-in stored procedures. While sp_spaceused provides detailed space information, it works on individual tables. The undocumented sp_msForEachTable allows us to iterate over all tables.

Basic Implementation

SET NOCOUNT ON;

-- Update usage statistics for accurate results
DBCC UPDATEUSAGE(0);

-- Get overall database size
EXEC sp_spaceused;

-- Create temporary table to store results
CREATE TABLE #TableSizes (
    [TableName] NVARCHAR(128),
    [Rows] CHAR(11),
    [Reserved] VARCHAR(18),
    [Data] VARCHAR(18),
    [IndexSize] VARCHAR(18),
    [Unused] VARCHAR(18)
);

-- Populate table with space information for each table
INSERT INTO #TableSizes 
EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''';

-- Display results sorted by data size
SELECT 
    TableName,
    CAST([Rows] AS INT) AS RowCount,
    [Reserved] AS ReservedSpace,
    [Data] AS DataSpace,
    [IndexSize] AS IndexSpace,
    [Unused] AS UnusedSpace
FROM #TableSizes
ORDER BY CAST(REPLACE([Data], ' KB', '') AS INT) DESC;

-- Show total statistics
SELECT 
    COUNT(*) AS TotalTables,
    SUM(CAST([Rows] AS INT)) AS TotalRows,
    SUM(CAST(REPLACE([Reserved], ' KB', '') AS INT)) AS TotalReservedKB,
    SUM(CAST(REPLACE([Data], ' KB', '') AS INT)) AS TotalDataKB,
    SUM(CAST(REPLACE([IndexSize], ' KB', '') AS INT)) AS TotalIndexKB
FROM #TableSizes;

DROP TABLE #TableSizes;

Method 2: Using System Views (Modern Approach)

SQL Server 2005 and later versions provide system views that offer more efficient and flexible ways to query table sizes.

Basic System View Query

SELECT 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    p.rows AS RowCount,
    SUM(au.total_pages) * 8 / 1024.0 AS TotalSpaceMB,
    SUM(au.used_pages) * 8 / 1024.0 AS UsedSpaceMB,
    (SUM(au.total_pages) - SUM(au.used_pages)) * 8 / 1024.0 AS UnusedSpaceMB,
    SUM(au.data_pages) * 8 / 1024.0 AS DataSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON p.partition_id = au.container_id
WHERE t.type = 'U'
    AND i.object_id > 255
GROUP BY t.schema_id, t.name, p.rows
ORDER BY SUM(au.total_pages) DESC;

Comprehensive System View Query

WITH TableSizes AS (
    SELECT 
        SCHEMA_NAME(t.schema_id) AS SchemaName,
        t.name AS TableName,
        t.object_id,
        SUM(CASE WHEN i.index_id IN (0,1) THEN p.rows ELSE 0 END) AS RowCount,
        SUM(au.total_pages) AS TotalPages,
        SUM(au.used_pages) AS UsedPages,
        SUM(au.data_pages) AS DataPages,
        SUM(CASE WHEN i.index_id > 1 THEN au.used_pages ELSE 0 END) AS IndexPages
    FROM sys.tables t
    INNER JOIN sys.indexes i ON t.object_id = i.object_id
    INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units au ON p.partition_id = au.container_id
    WHERE t.type = 'U'
    GROUP BY t.schema_id, t.name, t.object_id
),
TableStats AS (
    SELECT 
        SchemaName,
        TableName,
        RowCount,
        TotalPages * 8.0 / 1024 AS TotalSizeMB,
        UsedPages * 8.0 / 1024 AS UsedSizeMB,
        DataPages * 8.0 / 1024 AS DataSizeMB,
        IndexPages * 8.0 / 1024 AS IndexSizeMB,
        (TotalPages - UsedPages) * 8.0 / 1024 AS UnusedSizeMB
    FROM TableSizes
)
SELECT 
    SchemaName + '.' + TableName AS FullTableName,
    FORMAT(RowCount, 'N0') AS [Rows],
    FORMAT(TotalSizeMB, 'N2') + ' MB' AS [Total Size],
    FORMAT(DataSizeMB, 'N2') + ' MB' AS [Data Size],
    FORMAT(IndexSizeMB, 'N2') + ' MB' AS [Index Size],
    FORMAT(UnusedSizeMB, 'N2') + ' MB' AS [Unused Size],
    CASE 
        WHEN TotalSizeMB > 0 
        THEN FORMAT((DataSizeMB / TotalSizeMB) * 100, 'N1') + '%'
        ELSE '0%'
    END AS [Data %],
    CASE 
        WHEN TotalSizeMB > 0 
        THEN FORMAT((IndexSizeMB / TotalSizeMB) * 100, 'N1') + '%'
        ELSE '0%'
    END AS [Index %],
    CASE 
        WHEN RowCount > 0 
        THEN FORMAT((DataSizeMB * 1024 * 1024) / RowCount, 'N0') + ' bytes'
        ELSE '0 bytes'
    END AS [Avg Row Size]
FROM TableStats
WHERE TotalSizeMB > 0.01  -- Filter out very small tables
ORDER BY TotalSizeMB DESC;

Method 3: Quick and Efficient Query

For most scenarios, this simplified query provides the essential information:

-- Quick table size query (copy-paste ready)
SELECT 
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS [Table],
    FORMAT(SUM(ps.row_count), 'N0') AS [Rows],
    FORMAT(SUM(ps.reserved_page_count) * 8.0 / 1024, 'N2') + ' MB' AS [Size],
    FORMAT(SUM(ps.used_page_count) * 8.0 / 1024, 'N2') + ' MB' AS [Used],
    FORMAT((SUM(ps.reserved_page_count) - SUM(ps.used_page_count)) * 8.0 / 1024, 'N2') + ' MB' AS [Unused]
FROM sys.tables t
JOIN sys.dm_db_partition_stats ps ON t.object_id = ps.object_id
WHERE t.type = 'U'
GROUP BY t.schema_id, t.name
ORDER BY SUM(ps.reserved_page_count) DESC;

Performance Considerations

Low-Impact Query for Production

-- Optimized query with minimal performance impact
SELECT 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    SUM(ps.row_count) AS RowCount,
    SUM(ps.reserved_page_count) * 8.0 / 1024 AS ReservedMB,
    SUM(ps.used_page_count) * 8.0 / 1024 AS UsedMB
FROM sys.tables t
INNER JOIN sys.dm_db_partition_stats ps ON t.object_id = ps.object_id
WHERE t.type = 'U'
GROUP BY t.schema_id, t.name
ORDER BY SUM(ps.reserved_page_count) DESC;

Automation with PowerShell

# PowerShell script for automated table size monitoring
Import-Module SqlServer

$serverName = "localhost"
$databaseName = "YourDatabase"

$sqlQuery = @"
SELECT 
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS FullTableName,
    SUM(ps.row_count) AS RowCount,
    SUM(ps.reserved_page_count) * 8.0 / 1024 AS TotalSizeMB
FROM sys.tables t
INNER JOIN sys.dm_db_partition_stats ps ON t.object_id = ps.object_id
WHERE t.type = 'U'
GROUP BY t.schema_id, t.name
ORDER BY SUM(ps.reserved_page_count) DESC;
"@

$results = Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $sqlQuery

$results | Format-Table -AutoSize

Best Practices

  1. Run DBCC UPDATEUSAGE before using sp_spaceused for accurate results
  2. Use system views for better performance in production environments
  3. Schedule regular monitoring to track growth trends
  4. Filter small tables when analyzing large databases
  5. Consider index usage when interpreting size data

Conclusion

Multiple methods exist for finding table sizes in SQL Server, each with its own advantages:

  • sp_spaceused with sp_msForEachTable: Traditional, detailed, but slower
  • System Views: Modern, efficient, and flexible
  • Quick Queries: Fast and practical for most scenarios

Choose the method that best fits your needs, considering factors like performance impact, level of detail required, and automation requirements.

Quick Reference

The most practical query for daily use:

SELECT 
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS [Table],
    FORMAT(SUM(ps.row_count), 'N0') AS [Rows],
    FORMAT(SUM(ps.reserved_page_count) * 8.0 / 1024, 'N2') + ' MB' AS [Size]
FROM sys.tables t
JOIN sys.dm_db_partition_stats ps ON t.object_id = ps.object_id
WHERE t.type = 'U'
GROUP BY t.schema_id, t.name
ORDER BY SUM(ps.reserved_page_count) DESC;

```sql SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

– Find DB size. EXEC sp_spaceused

– Create a table to counts row and sizes. CREATE TABLE #tbl ( [name] NVARCHAR(128), [rows] CHAR(11),

Comments

You May Also Enjoy