Index

  • I have 1000 Indexes on Primary

    I have database MDF data file  in 2 drives on E Drive and F drive.

    I want to know which Indexes in E drive and F drive.

    How do I get that info?

  • if you have a single filegroup with data files then you can't really tell it - some pages may be on one file, other pages on another file. So although you could find out where a particular page is, that isn't the full index and would not help at all.

    in any case this sql can give you some info

    select ds.name as dataspacename
    , f.physical_name
    , au.type_desc as allocationdesc
    , au.total_pages / 128 as totalsizemb
    , au.used_pages / 128 as usedsizemb
    , au.data_pages / 128 as datasizemb
    , sch.name as schemaname
    , obj.type_desc as objecttype
    , obj.name as objectname
    , idx.type_desc as indextype
    , idx.name as indexname
    from sys.data_spaces as ds
    inner join sys.allocation_units as au
    on ds.data_space_id = au.data_space_id
    inner join sys.partitions as pa
    on (au.type in (1, 3)
    and au.container_id = pa.hobt_id)
    or (au.type = 2
    and au.container_id = pa.partition_id)
    join sys.database_files f
    on au.data_space_id = f.data_space_id
    inner join sys.objects as obj
    on pa.object_id = obj.object_id
    inner join sys.schemas as sch
    on obj.schema_id = sch.schema_id
    left join sys.indexes as idx
    on pa.object_id = idx.object_id
    and pa.index_id = idx.index_id
    where objectproperty(obj.object_id, 'IsUserTable') = 1
  • Thanks this helped me kind of what I am looking for

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply