Check SQL Server Table Size: Quick Guide

1 year ago
William Carter
2 minutes

In SQL Server, you have a few methods to view the size of table spaces.

  1. You can use the system stored procedure sp_spaceused to retrieve information on the space usage of all tables in the database. Simply execute the following statement in the query window to get the size information of tables.
EXEC sp_spaceused;
  1. You can obtain partition statistical information for all tables and indexes in a database using the system view sys.dm_db_partition_stats. To retrieve information on the size of tables, you can use the following query.
SELECT 
    OBJECT_NAME(object_id) AS [TableName],
    SUM(used_page_count) * 8 AS [TableSizeKB]
FROM 
    sys.dm_db_partition_stats
WHERE 
    index_id IN (0, 1) -- 0 是堆表，1 是聚集索引
GROUP BY 
    object_id
ORDER BY 
    [TableSizeKB] DESC;

This query will return the name of each table and its size in kilobytes.

  1. You can get information about the size of table spaces using the system view sys.allocation_units, which provides details about all allocation units in the database, including those for tables, indexes, and heaps.
SELECT 
    OBJECT_NAME(object_id) AS [TableName],
    SUM(total_pages) * 8 AS [TableSizeKB]
FROM 
    sys.allocation_units
WHERE 
    type IN (1, 3) -- 1 是 IN_ROW_DATA 用于表和索引，3 是 LOB_DATA 用于大型对象（例如：文本和图像）
GROUP BY 
    object_id
ORDER BY 
    [TableSizeKB] DESC;

This query will retrieve the name of each table and the space it occupies, measured in kilobytes.

Please note that the space size returned by the above method is an approximation and may differ from the space size reported at the operating system level. This is because SQL Server uses pages to manage data, which incurs some additional overhead during page allocation and deallocation.

bannerAds