查询表数据行长度信息、数据和索引占用空间

查询表数据行长度信息、数据和索引占用空间

------------------------------------------------------------------------

-- 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

       LEFT OUTER JOIN

       (

           SELECT it.parent_id,

           SUM(ps.reserved_page_count) AS reserved,

           SUM(ps.used_page_count) AS used

           FROM sys.dm_db_partition_stats ps

           INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)

           WHERE it.internal_type IN (202,204)

           GROUP BY it.parent_id

       ) AS a4 ON (a4.parent_id = a1.object_id)

    INNER JOIN (SELECT id,

               sum(length) AS length

               FROM sys.syscolumns

               GROUP BY id) d ON d.id = a1.object_id

    INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )

    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

    WHERE a2.type <> N'S' and a2.type <> N'IT'

    ORDER BY ID DESC

 

--效果：

/*

Schema   表称       行数    理论每    平均每条  保留空间  数据使用   索引使用  未用

                            条记录长度  记录长度   (K)      空间(k)  空间(k)  空间(k)

------ -------- ------- ---------- -------- ------ ------- ----- --------

dbo     ta              2        20       4096     16       8        8        0

dbo     category        8        14       1024     16       8        8        0

dbo     test2           6        4096     16       8        8        0

dbo     DiguiTable      13       3008     630      16       8        8        0

dbo     tc              4        14       2048     16       8        8        0

dbo     tb              7        52       1170     16       8        8        0

dbo     contactKeyword  7        8        1170     16       8        8        0

dbo     keyword         5        6        1638     16       8        8        0

dbo     contact         3        104      2730     16       8        8        0

dbo     xlstest         258      319      412      784      104      8        672

 

(10 行受影响)

*/