-- List tables, indexes & filegroups
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

-- These are undocumented SPs ,
-- so please use this only in admin routines
EXEC sp_MSforeachtable 
'DECLARE @objid int 
 SET @objid = OBJECT_ID( ''?'' ) 
 SELECT ''?'' AS Table_name 
 EXEC sp_objectfilegroup @objid'


-- Alternatively the filegroup information can be obtained
-- from the sp_help SP output also.
Exec sp_help [My Resource Files]

This page was last updated on May 01, 2006 04:28 PM.