Post reply

Tables inside Filegroups

  • December 2, 2008 at 11:49 pm

    #216033

    Hi,

    I do a database with a number of filegroups and with a number of tables inside them.

    Now i need to know which tables are lying under which filegroup.

    ANy help?

    thanks in adv

    manus

  • December 3, 2008 at 12:42 am

    #906670

    Sure. It's one of those not-so-intuitive queries (understatement)

    This will work providing you have no table partitioning. If you do, it may give strange results

    select object_name(p.object_id), ds.name as FilegroupName

    from sys.partitions p

    inner join sys.allocation_units au on p.partition_id = au.allocation_unit_id

    inner join sys.data_spaces ds on au.data_space_id = ds.data_space_id

    where index_id in (0,1)

    This gives the location of the heap/clustered index. Other indexes may be on other partitions.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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