Post reply

Tables - Filegroups

  • November 20, 2007 at 3:39 am

    #69455

    Hi All

    I just want to know if one of you have a script that shows which table belongs to which filegroup?

    Thanks in advance

    A

  • November 20, 2007 at 4:16 am

    #752578

    Here is the query, would give which indexes are stored on which filegroups...

    SELECTOBJECT_NAME( i.id ) AS TableName, i.name AS IndexName, fg.groupname AS [FileGroup]

    FROMsysindexes i

    INNER JOIN sysfilegroups fg ON i.groupid = fg.groupid

    --Ramesh

  • November 20, 2007 at 4:17 am

    #752579

    Anchelin (11/20/2007)

    Hi All

    I just want to know if one of you have a script that shows which table belongs to which filegroup?

    Thanks in advance

    A

    On 2000:

    SELECT USER_NAME(o.uid) AS owner

    , o.name

    , sysfilegroups.groupname AS FileGroup

    FROM sysobjects AS o

    LEFT JOIN sysindexes WITH ( NOLOCK ) ON sysindexes.id = o.id

    LEFT JOIN sysfilegroups WITH ( NOLOCK ) ON sysfilegroups.groupid = sysindexes.groupid

    WHERE OBJECTPROPERTY(o.id, N'IsTable') = 1

    AND sysindexes.indid<2

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • November 20, 2007 at 4:21 am

    #752581

    select Dataonfilegroup = s.groupname

    from sysfilegroups s, sysindexes i

    where i.id = object_ID('oRDERS')

    and i.indid < 2

    and i.groupid = s.groupid

  • November 20, 2007 at 5:03 am

    #752601

    Thank you all for your replies. Much appreciated

Viewing 5 posts - 1 through 4 (of 4 total)

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