Understanding "Get Size of All Tables in a Database" in SQL Server

What does it mean?

When you say "Get size of all tables in a database", you're essentially asking for a report that shows the storage space occupied by each table within a specific SQL Server database. This information is crucial for database management tasks such as:

• Identifying large tables: To optimize query performance or storage utilization.
• Monitoring database growth: To plan for future storage needs or identify potential issues.
• Database maintenance: To determine which tables might benefit from indexing, partitioning, or data compression.

How is it done in SQL Server (using T-SQL)?

SQL Server provides several ways to obtain table sizes. Here are the common approaches:

Using sp_spaceused System Stored Procedure:

• This is a quick and easy method to get basic information about table size.
• It provides details like data and index size in pages.

EXEC sp_spaceused 'YourTableName';

Querying System Catalog Views:

• Offers more granular control over the information retrieved.
• Involves joining multiple system views to calculate table size.

SELECT 
    t.name AS TableName,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB
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 a ON p.partition_id = a.container_id
WHERE 
    t.is_ms_shipped = 0    -- Exclude system tables
GROUP BY 
    t.name;

Using DBCC SHOWCONTIG command:

• Provides detailed information about data and index fragmentation.
• Can be used to calculate table size but is generally less efficient than the previous methods.

Important Considerations:

• Data Types: The results of these queries typically show size in pages or kilobytes. You might need to convert it to megabytes or gigabytes for better readability.
• Data Types and Indexes: The size of a table includes both data and indexes.
• Performance: For large databases, querying system catalog views might be resource-intensive. Consider using indexes or performance hints to improve query performance.

Additional Tips:

• Regular Monitoring: Implement a scheduled job to track table sizes over time.
• Visualization: Use graphical tools to visualize table size trends.
• Data Compression: Explore data compression options to reduce table size.
• Indexing: Optimize indexing to improve query performance and potentially reduce index size.

By understanding these methods and considerations, you can effectively determine the size of tables in your SQL Server database and make informed decisions about database management.

Understanding the Code Examples for Getting Table Sizes in SQL Server

Example 1: Using sp_spaceused

EXEC sp_spaceused 'YourTableName';

This code executes the built-in system stored procedure sp_spaceused for a specific table named 'YourTableName'. This procedure returns information about the table's storage usage, including:

• database_name: The database where the table resides.
• name: The name of the table.
• rows: The number of rows in the table.
• reserved: The total space reserved for the table (in pages).
• data: The space used by data (in pages).
• index_size: The space used by indexes (in pages).
• unused: The unused space in the table (in pages).

SELECT 
    t.name AS TableName,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB
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 a ON p.partition_id = a.container_id
WHERE 
    t.is_ms_shipped = 0    -- Exclude system tables
GROUP BY 
    t.name;

This code uses a more complex approach involving multiple system catalog views to calculate table size. It provides more detailed information about table storage, including:

• TotalSpaceKB: The total space allocated to the table in kilobytes.
• UsedSpaceKB: The space used by the table in kilobytes.

The code joins several system tables to gather information about the table, its indexes, partitions, and allocation units. The SUM function is used to calculate the total space based on the number of pages.

Key Points

• Both methods provide information about table size, but the second method offers more granular details.
• For large databases, the second method might be resource-intensive.
• Consider using indexes or performance hints to improve query performance when using the second method.

To get the size of all tables in a database, you can modify the second example by removing the WHERE clause that excludes system tables.

To convert the size to megabytes, you can divide the result by 1024 twice:

SELECT 
    t.name AS TableName,
    SUM(a.total_pages) * 8 / 1024 / 1024 AS TotalSpaceMB,
    SUM(a.used_pages) * 8 / 1024 / 1024 AS UsedSpaceMB
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 a ON p.partition_id = a.container_id   
GROUP BY 
    t.name;

Alternative Methods for Getting Table Sizes in SQL Server

While the methods we've discussed (using sp_spaceused and querying system catalog views) are common and effective, there are a few other approaches you can consider:

Using Dynamic Management Views (DMVs)

DMVs provide real-time information about the SQL Server instance. While not as commonly used for this specific task, they can be explored. However, the information provided by DMVs might not be as comprehensive as the traditional methods.

Using Extended Stored Procedures

Extended stored procedures are DLLs that can be executed from within SQL Server. They offer more flexibility but require additional development and deployment steps. There's typically no built-in extended stored procedure for this specific task, so you would need to create one yourself.

Using Third-Party Tools

Several third-party database management tools offer features to analyze table sizes and database space usage. These tools often provide graphical interfaces and additional insights beyond basic size information.

• Performance: The performance implications of different methods can vary significantly, especially for large databases.
• Accuracy: The accuracy of the results might differ slightly between methods due to factors like data page fill, internal fragmentation, and other database engine internals.
• Granularity: The level of detail provided by each method can vary. Some methods might only give you total table size, while others can break down the size by data, indexes, or even individual columns.
• Ease of Use: The simplicity of implementation and use is another factor to consider.