Get a listing of table names on a particular file group

• CanuckBuck

  Hall of Fame

  Points: 3915

  More actions

  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.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  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!

• CanuckBuck

  Hall of Fame

  Points: 3915

  More actions

  December 3, 2009 at 7:03 am

  #1087050

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