Sql-server – Find what data is placed in a datafile

datafilefilegroupsfilessql serversql-server-2008

I have a database with 5 .ndf (secondary) datafiles on SQL Server 2008 R2. (Each of these datafiles is assigned to a separate filegroup.) I want to know what data is there in those files. Is it possible? I know there are full text indexes in these filegroups (and since there is only one file per filegroup maybe the sole purpose of creating this many files were to place full text indexes in different filegroups, I don't know). I want to make sure there is no data other than full text indexes.
Thank you

Best Answer

The following SQL will show you which file groups your tables and indexes are in which will make it easy to see if there is data in any file group that shouldn't have data.

SELECT      f.[name] AS FileGroupName
            , o.[name] AS ObjectName
            , o.[type] AS [Type]
            , i.[name] AS IndexNAme
            , i.[index_id] AS IndexId

FROM        sys.indexes i
INNER JOIN  sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN  sys.all_objects o ON i.[object_id] = o.[object_id]

WHERE       i.data_space_id = f.data_space_id
            AND o.type = 'U' -- User Created Tables

ORDER BY    f.[name]
            , o.[name] 
            , o.[type] 
            , i.[name]

full details of the script can be seen here

Related Question