Sql Server中查询数据库所有表及其数据总条数和占用空间

Shaw0xyz

在日常数据库管理中，了解数据库中各表的数据总条数和占用空间情况是非常重要的。这有助于优化数据库性能、进行容量规划和数据维护。本文将详细介绍如何在 Sql Server 中查询数据库所有表的数据总条数和占用空间。

1. 查询数据库所有表及其数据总条数

要查询数据库中所有表的数据总条数，我们可以利用系统视图 `sys.tables` 和 `sys.partitions` 以及聚合函数。以下是一个完整的 SQL 查询示例：

1. 
   
2. SELECT
   
3.     t.NAME AS TableName,
   
4.     p.rows AS RowCounts
   
5. FROM
   
6.     sys.tables t
   
7. INNER JOIN      
   
8.     sys.partitions p ON t.object_id = p.object_id
   
9. WHERE
   
10.     p.index_id IN (0, 1)
    
11. ORDER BY
    
12.     t.NAME;

复制代码

1.1 解释

(1) `sys.tables`：包含数据库中所有表的基本信息。

(2) `sys.partitions`：包含每个表或索引的分区信息。

(3) `p.index_id IN (0, 1)`：过滤出表的数据分区（0 表示堆表，1 表示聚集索引表）。

(4) `p.rows`：表示表中的行数。

上述查询将返回数据库中所有表的名称及其行数。

2. 查询数据库所有表及其占用空间

为了获取数据库中所有表的占用空间信息，可以使用 `sys.dm_db_partition_stats` 和 `sys.tables` 系统视图。以下是一个完整的 SQL 查询示例：

1. 
   
2. SELECT
   
3.     t.NAME AS TableName,
   
4.     SUM(a.total_pages) * 8 AS TotalSpaceKB,
   
5.     SUM(a.used_pages) * 8 AS UsedSpaceKB,
   
6.     (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
   
7. FROM
   
8.     sys.tables t
   
9. INNER JOIN      
   
10.     sys.indexes i ON t.object_id = i.object_id
    
11. INNER JOIN
    
12.     sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    
13. INNER JOIN
    
14.     sys.allocation_units a ON p.partition_id = a.container_id
    
15. GROUP BY
    
16.     t.NAME
    
17. ORDER BY
    
18.     TotalSpaceKB DESC;

复制代码

2.1 解释

(1) `sys.indexes`：包含每个表和视图的索引信息。

(2) `sys.partitions`：包含每个表或索引的分区信息。

(3) `sys.allocation_units`：包含每个分区或大对象数据的存储单元信息。

(4) `a.total_pages`：表示分配的总页数。

(5) `a.used_pages`：表示已使用的页数。

(6) 每个页的大小为 8KB，因此将页数乘以 8 得到空间大小（以 KB 为单位）。

上述查询将返回数据库中所有表的名称及其占用的总空间、已使用空间和未使用空间。

3. 综合查询：表的数据总条数和占用空间

为了同时获取表的数据总条数和占用空间，可以将上述两个查询结合起来。以下是一个完整的 SQL 查询示例：

1. 
   
2. SELECT
   
3.     t.NAME AS TableName,
   
4.     p.rows AS RowCounts,
   
5.     SUM(a.total_pages) * 8 AS TotalSpaceKB,
   
6.     SUM(a.used_pages) * 8 AS UsedSpaceKB,
   
7.     (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
   
8. FROM
   
9.     sys.tables t
   
10. INNER JOIN
    
11.     sys.indexes i ON t.object_id = i.object_id
    
12. INNER JOIN
    
13.     sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    
14. INNER JOIN
    
15.     sys.allocation_units a ON p.partition_id = a.container_id
    
16. WHERE
    
17.     p.index_id IN (0, 1)
    
18. GROUP BY
    
19.     t.NAME, p.rows
    
20. ORDER BY
    
21.     TotalSpaceKB DESC;

复制代码

3.1 解释

(1) 此查询结合了 `sys.tables`、`sys.indexes`、`sys.partitions` 和 `sys.allocation_units` 视图。

(2) `p.index_id IN (0, 1)`：过滤出表的数据分区，确保查询结果中仅包含表的主数据。

(3) 通过 `GROUP BY t.NAME, p.rows` 确保按表名和行数分组，并对每个表进行空间计算。

此查询将返回数据库中所有表的名称、行数、总空间、已使用空间和未使用空间，按总空间大小降序排列。

4. 总结

本文详细介绍了如何在 Sql Server 中查询数据库所有表及其数据总条数和占用空间。通过结合系统视图和聚合函数，可以轻松获取这些信息，从而更好地管理和优化数据库。希望本文能帮助您在实际项目中更高效地使用 Sql Server。