SQL2000的sysindexes视图在SQL2005中的另类表现

 

-- 查找表和索引以及行数

    select object_name ( i . object_id ) as objectName , i . name as indexName , sum ( p . rows ) as rowCnt

    from sys . indexes i

    join sys . partitions p

    on   i . object_id = p . object_id

    and   i . index_id = p . index_id

    where i . object_id = '567777180'   --object_id('')

    and   i . index_id <= 1

    group by i . object_id , i . index_id , i . name

   

-- 使用的总页数 , 使用的页面 , 堆数据页以及索引等相关计数

    select object_name ( i . object_id ) as objectName , i . name as indexName ,

            sum ( a . total_pages ) as totalPages , sum ( a . used_pages ) as usedPages , sum ( a . data_pages ) as dataPages ,

            ( sum ( a . total_pages ) * 8) / 1024 as totalSpaceMB , ( sum ( a . used_pages ) * 8) / 1024 as usedSpaceMB , ( sum ( a . data_pages ) * 8) / 1024 as dataSpaceMB

    from sys . indexes i

    join sys . partitions p

    on   i . object_id = p . object_id

    and   i . index_id = p . index_id

    join sys . allocation_units a

    on   p . partition_id = a . container_id

    where i . object_id = '567777180'   --object_id('')

    and   i . index_id <= 1

    group by i . object_id , i . index_id , i . name

   

-- 使用的总页数 , 使用的页面 , 堆数据页以及索引等相关计数的分类显示

    select case when grouping ( i . object_id ) = 1 then '--- TOTAL ---' else object_name ( i . object_id ) end as objectName ,

            case when grouping ( i . name ) = 1 then '--- TOTAL ---' else i . name end as indexName ,

            case when grouping ( a . type_desc ) = 1 then '--- TOTAL ---' else a . type_desc end as pageType ,

            sum ( a . total_pages ) as totalPages , sum ( a . used_pages ) as usedPages , sum ( a . data_pages ) as dataPages ,

            ( sum ( a . total_pages ) * 8) / 1024 as totalSpaceMB , ( sum ( a . used_pages ) * 8) / 1024 as usedSpaceMB , ( sum ( a . data_pages ) * 8) / 1024 as dataSpaceMB

    from sys . indexes i

    join sys . partitions p

    on   i . object_id = p . object_id

    and   i . index_id = p . index_id

    join sys . allocation_units a

    on   p . partition_id = a . container_id

    where i . object_id = '567777180'   --object_id('')

    and   i . index_id <= 1

group by i . object_id , i . name , a . type_desc with rollup

 

注：文档中的 '567777180'   是 sys . objects 表中 object_id 。