SQL Server Performance

how to get the filgroups for all tables in a datab

Discussion in 'Performance Tuning for DBAs' started by cchitanu, May 7, 2004.

  1. cchitanu New Member

    Does anybody have a script to get the filgroups for all tables in a database
    Thanks
  2. gaurav_bindlish New Member

    Try exploring sp_help for the same. There is a section where it shells out the filegroup of the table. Use sp_MSforeachtable to execute that part for all the tables.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. ykchakri New Member

    An sp_help on a table shows which filegroup the table is in. The 'Data_located_on_filegroup' column in the sp_hep results shows this. You can write a simple cursor to show this column value for all tables.
  4. Luis Martin Moderator

    I use this one to find also index and statistics, you can change it to only tables.

    select si.rows as 'filas', SO.Name as Tabla, SI.name as 'Index', SFG.groupname as 'Filegroup'
    from sysobjects as SO
    join sysindexes as SI
    on SO.Id = SI.id
    join sysfilegroups as SFG
    on SI.GroupId = SFG.GroupId
    order by SFG.GroupName, SO.Name , SI.name

    Luis Martin
    Moderator
    SQL-Server-Performance.com

  5. cchitanu New Member

  6. satya Moderator

    I think if you can have a look at SP_HELPFILEGROUP, you can have an idea about how to approach.'
    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page