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,

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

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