Post reply

Get a listing of table names on a particular file group

  • December 3, 2009 at 6:24 am

    #98422

    Hello.

    Is there a way to get a listing of tables on a particular file group from the INFORMATION_SCHEMA views? I've poked around in the system tables and found a table called sysfilegroups. I don't know (yet) if/how I can join this table to INFORMATION_SCHEMA tables to get what I want.

    Thanks in advance for any assistance that anyone can provide.

  • December 3, 2009 at 6:39 am

    #1087032

    here you go: i had this saved in my SQL2000 snippets.

    it produces results like this:

    TableName IndexName FileGroupName

    CacheData PK_CacheData PRIMARY

    CENSUSTRACTS NULL PRIMARY

    CMACCOUNT PK__SFACCT__4D35603F PRIMARY

    here's the script:

    SELECT OBJECT_NAME( i."id" ) AS TableName ,

    i."Name" AS IndexName ,

    FILEGROUP_NAME( i.groupid ) AS FileGroupName

    FROM sysindexes AS i

    WHERE ( i.indid IN ( 0 , 1 ) Or i.indid < 255 ) And -- Tables & indexes only

    OBJECTPROPERTY( i."id" , 'IsUserTable' ) = 1 And -- User tables only

    OBJECTPROPERTY( i."id" , 'IsMSShipped' ) = 0 And -- No system tables

    COALESCE( INDEXPROPERTY( i."id" , i."Name" , 'IsStatistics' ) , 0 ) = 0 And -- No Statistics / Auto-Create stats

    COALESCE( INDEXPROPERTY( i."id" , i."Name" , 'IsHypothetical' ) , 0 ) = 0 -- No Hypothetical statistics

    ORDER BY FileGroupName , TableName , IndexName

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 3, 2009 at 7:03 am

    #1087050

    That's exactly what I'm looking for. Thanks!

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

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