ScriptToFindWhichTablesBelongToWhichFileGroups

If we give SP_HELP , we'll know to which File Group the table belongs.
If we give SP_HELPFILEGROUP , we'll know the logical and physical file names along with other information that belong to the given File Group.
But have you ever tried that, given a file group, how to find all the tables that belong to that file group?
OR You want to know what are all the tables that belong to each file group. Here are two different ways of querying such possibilities.

--Author: Krishna Sonti --Created Date: 5/10/2004 --1. Given a File Group Name, how to find all the tables -- that belong the file group CREATE PROC usp_FilegroupObjects @FGName sysname AS SELECT s.groupname AS FileGroupName, object_name(id) AS ObjectName FROM SYSFILEGROUPS s, SYSINDEXES i WHERE i.indid < 2 AND i.groupid = s.groupid AND s.groupname = @FGname ORDER BY object_name(id) /******* --Sample output from my database FileGroupName ObjectName ------------- ---------- FileGroup1 authors FileGroup1 discounts FileGroup1 employee FileGroup1 jobs FileGroup1 pub_info FileGroup1 publishers *******/ --2. To find all the tables that belong to each file group CREATE PROC usp_AllFilegroupsObjects AS SELECT s.groupname AS GroupName, object_name(id) AS ObjectName FROM SYSFILEGROUPS s, SYSINDEXES i WHERE i.indid < 2 AND i.groupid = s.groupid AND s.groupname IN ( 'Filegroup1', 'Filegroup2', 'PRIMARY') --You need to mention your File Groups here in the 'IN Clause' ORDER BY s.groupname /******* --Sample output from my database FileGroupName ObjectName ------------- ---------- FileGroup1 discounts FileGroup1 employee FileGroup1 authors FileGroup1 pub_info FileGroup1 publishers FileGroup1 jobs FileGroup2 stores FileGroup2 titles FileGroup2 roysched FileGroup2 sales FileGroup2 titleauthor PRIMARYsysobjects PRIMARYsysindexes PRIMARYsyscolumns PRIMARYsystypes PRIMARYsyscomments PRIMARYsysfiles1 PRIMARYsyspermissions PRIMARYsysusers PRIMARYsysproperties PRIMARYsysdepends PRIMARYsysreferences PRIMARYsysfulltextcatalogs PRIMARYsysfulltextnotify PRIMARYsysfilegroups PRIMARYdtproperties *******/