Help with query to list DB objects, locations etc

• rarara

  SSCommitted

  Points: 1538

  More actions

  October 1, 2012 at 9:54 am

  #282373

  Hi, I am trying to see which heavily used database tables and indexes reside on which particular file groups to try and then get to the bottom of disk IO issues. Does anyone have a ready-made script to help? I have tried myself but my poor scripting isn't giving what I want. Ideally i'd like the following user items listed:

  Table

  Index type (clust, non-clust, heap if none)

  Index name

  Filegroup

  Phyical file

  Physical file logical name

  Row count

  Table size/index size MB

  Any help appreciated

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  October 1, 2012 at 10:39 am

  #1544056

  Here is a start to obtain some if not most of what you require

  http://sqlblogcasts.com/blogs/davidwimbush/archive/2009/07/28/how-to-get-the-logical-and-physical-file-names-for-a-database.aspx

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]