![](https://csdnimg.cn/release/download_crawler_static/86705732/bg1.jpg)
查询表数据行长度信息、数据和索引占用空间
------------------------------------------------------------------------
-- Author : HappyFlyStone
-- Date : 2009-09-09
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
-- 更多关注 :blog.csdn.net/happyflystone 转载注明出处!
------------------------------------------------------------------------
SELECT a3.name AS [Schema 名称],
a2.name AS [表称],
a1.rows as [行数],
length 理论每条记录长度,
a1.data * 8*1024/(CASE WHEN a1.Rows=0 THEN 1 ELSE a1.Rows END) [平均每条记
录长度],
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS [保留空间(K)],
a1.data * 8 AS [数据使用空间(k)],
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data
THEN (a1.used + ISNULL(a4.used,0)) - a1.data
ELSE 0 END) * 8 AS [索引使用空间(k)],
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used
THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used
ELSE 0 END) * 8 AS [未用空间(k)]
FROM(
SELECT
ps.object_id,
SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0
END) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (CASE WHEN (ps.index_id < 2) THEN
(ps.in_row_data_page_count + ps.lob_used_page_count +
ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count +
ps.row_overflow_used_page_count) END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1