Post reply

Index

  • January 31, 2020 at 9:07 pm

    #3722152

    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?

  • January 31, 2020 at 9:23 pm

    #3722154

    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

  • January 31, 2020 at 9:50 pm

    #3722167

    Thanks this helped me kind of what I am looking for

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

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