Home Forums SQL Server 7,2000 Strategies Get a listing of table names on a particular file group RE: Get a listing of table names on a particular file group

  • 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!