sql server 查看資料表占用儲存空間大小

查看指定資料表使用的儲存空間資訊

查看 users 資料表大小：

    
EXEC sp_spaceused N'users';
    

範例輸出：

    
EXEC sp_spaceused N'users';
+-----+--------------------+--------+-----+----------+------+
|name |rows                |reserved|data |index_size|unused|
+-----+--------------------+--------+-----+----------+------+
|Users|96                  |136 KB  |88 KB|16 KB     |32 KB |
+-----+--------------------+--------+-----+----------+------+
    

查看所有資料表的儲存空間資訊

查看所有資料表的儲存空間資訊：

    
SELECT * FROM sys.dm_db_partition_stats;
+---------------+---------+--------+----------------+----------------------+----------------------+--------------------------+-------------------+-----------------------+----------------------------+--------------------------------+---------------+-------------------+---------+
|partition_id   |object_id|index_id|partition_number|in_row_data_page_count|in_row_used_page_count|in_row_reserved_page_count|lob_used_page_count|lob_reserved_page_count|row_overflow_used_page_count|row_overflow_reserved_page_count|used_page_count|reserved_page_count|row_count|
+---------------+---------+--------+----------------+----------------------+----------------------+--------------------------+-------------------+-----------------------+----------------------------+--------------------------------+---------------+-------------------+---------+
|196608         |3        |1       |1               |36                    |38                    |49                        |0                  |0                      |0                           |0                               |38             |49                 |2116     |
|327680         |5        |1       |1               |4                     |6                     |13                        |0                  |0                      |0                           |0                               |6              |13                 |263      |
|281474977103872|6        |1       |1               |0                     |0                     |0                         |0                  |0                      |0                           |0                               |0              |0                  |0        |
|458752         |7        |1       |1               |6                     |8                     |12                        |0                  |0                      |0                           |0                               |8              |12                 |347      |
|562949953880064|7        |2       |1               |1                     |2                     |2                         |0                  |0                      |0                           |0                               |2              |2                  |347      |
+---------------+---------+--------+----------------+----------------------+----------------------+--------------------------+-------------------+-----------------------+----------------------------+--------------------------------+---------------+-------------------+---------+

    

但是從上面的資訊我們很難分辨出這是哪個資料表，需要經過一些處理：

    
SELECT tables.name                                  AS TableName,
       schemas.Name                                 AS SchemaName,
       SUM(a.total_pages) * 8                       AS TotalSpaceKB, -- 總空間（KB）
       SUM(a.used_pages) * 8                        AS UsedSpaceKB,  -- 已使用空間（KB）
       (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB -- 未使用空間（KB）
FROM sys.tables tables
         INNER JOIN sys.schemas schemas ON tables.schema_id = schemas.schema_id
         INNER JOIN sys.indexes indexes ON tables.object_id = indexes.object_id
         INNER JOIN sys.partitions partitions ON indexes.object_id = partitions.object_id AND indexes.index_id = partitions.index_id
         INNER JOIN sys.allocation_units a ON partitions.partition_id = a.container_id
GROUP BY tables.name, schemas.Name
ORDER BY TotalSpaceKB DESC;
    

範例輸出結果：

    
+---------+----------+------------+-----------+-------------+
|TableName|SchemaName|TotalSpaceKB|UsedSpaceKB|UnusedSpaceKB|
+---------+----------+------------+-----------+-------------+
|Users    |dbo       |136         |104        |32           |
|Roles    |dbo       |72          |32         |40           |
+---------+----------+------------+-----------+-------------+
    

就可以快速查看資料庫中的各個資料表占用的儲存空間了！

參考資料：
Microsoft.Learn - sp_spaceused (Transact-SQL)
Microsoft.Learn - sys.dm_db_partition_stats (Transact-SQL)