filegroup get all DB tables and views info

-- Get all DB tables and views filegroup info
SET NOCOUNT ON

-- This table will hold the collected filegroup information
IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo

CREATE TABLE #tblFileGroupInfo(
TableOrViewName NVARCHAR(128),
[Table/View] VARCHAR(16),
AttributeName NVARCHAR(128),
AttributeType VARCHAR(128),
AttributeIndid INT,
FileGroupName NVARCHAR(128),
FileGroupID INT)


-- Get the file group information for all indexes of all tables and indexedviews
-- on the database. Note that auto-statistics created by SQL server are excluded,
-- since they are hardly of interest.
INSERT INTO #tblFileGroupInfo(
TableOrViewName,
[Table/View],
AttributeName,
AttributeType,
AttributeIndid,
FileGroupName,
FileGroupID)
SELECT a.TABLE_NAME,
CASE WHEN a.TABLE_TYPE = 'VIEW' THEN 'View'
ELSE 'Table'
END,
ISNULL(b.[name], 'NA'),
CASE WHEN b.indid = 0 THEN 'Table Row Data (table has no
clustered index)'
WHEN b.indid = 1 THEN 'Clustered Index (and table row data)'
WHEN b.indid = 255 THEN 'TEXT/NTEXT/IMAGE/XML Column Data'
ELSE 'Non Clustered Index'
END,
b.indid,
FILEGROUP_NAME(b.groupid),
b.groupid
FROM INFORMATION_SCHEMA.TABLES a WITH (NOLOCK)
INNER JOIN sysindexes b WITH (NOLOCK)
ON OBJECT_ID(a.TABLE_NAME) = b.[id]
WHERE INDEXPROPERTY(b.[id], b.[name], 'IsAutoStatistics') = 0


SET NOCOUNT OFF

SELECT *
FROM #tblFileGroupInfo WITH (NOLOCK)
ORDER BY TableOrViewName ASC, AttributeIndid ASC

-- Cleanup

IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo
GO


----------------------
--another simple way--
----------------------
-- to identofy which tables and indexes are used in which filegroup
select distinct(object_name(id)) from sysindexes where groupid=filegroup_id('Third')
select name from sysindexes where groupid=filegroup_id('Third') and indid > 0