SQL查询数据库对象大小：数据文件与索引详细分析

--查询当前数据库对象大小[M]


 SELECT db_name() AS [数据库名],
        a3.name AS [架构名称] ,
        a2.name AS [表名称] ,
        a1.rows AS [记录条数] ,
        ( a1.reserved + ISNULL(a4.reserved, 0) ) * 8 /1024 AS [保留空间(M)] ,
        a1.data * 8 /1024 AS [数据使用空间(M)] ,
        ( CASE WHEN ( a1.used + ISNULL(a4.used, 0) ) > a1.data
               THEN ( a1.used + ISNULL(a4.used, 0) ) - a1.data
               ELSE 0
          END ) * 8 /1024 AS [索引使用空间(M)] ,
        ( CASE WHEN ( a1.reserved + ISNULL(a4.reserved, 0) ) > a1.used
               THEN ( a1.reserved + ISNULL(a4.reserved, 0) ) - a1.used
               ELSE 0
          END ) * 8 /1024 AS [未用空间(M)] ,
        a1.data * 8 * 1024 / ( CASE WHEN a1.Rows = 0 THEN 1
                                    ELSE a1.Rows
                               END ) 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