Index

• Nita Reddy

  SSCarpal Tunnel

  Points: 4182

  More actions

  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?

• frederico_fonseca

  SSCoach

  Points: 16247

  More actions

  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
  

  • This reply was modified 5 years, 6 months ago by frederico_fonseca.

• Nita Reddy

  SSCarpal Tunnel

  Points: 4182

  More actions

  January 31, 2020 at 9:50 pm

  #3722167

  Thanks this helped me kind of what I am looking for