Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get a listing of table names on a particular file group Expand / Collapse
Author
Message
Posted Thursday, December 03, 2009 6:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 04, 2012 6:36 AM
Points: 96, Visits: 228
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.
Post #828101
Posted Thursday, December 03, 2009 6:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:27 PM
Points: 12,744, Visits: 31,080
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #828109
Posted Thursday, December 03, 2009 7:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 04, 2012 6:36 AM
Points: 96, Visits: 228
That's exactly what I'm looking for. Thanks!
Post #828129
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse