December 13, 2011 at 12:04 pm
I have inherited a large database that is split up into 125 different file groups. What I would like to do is document this using DMVs.
The output should have the following elements:
object name
object type (table, index, etc.)
file name
file path
file group name
The file name and path can be duplicated as there can be multiple files in a group, but this is OK as I plan to group it in a SSRS report.
I can find the sys.sysfiles and sys.sysfilegroups views and it looks like sys.sysfiles.group_id = sys.filegroups.data_space_id. I found the data_space_id in the sys.indexes, but how do I find out which file the actual data is in?
...
December 13, 2011 at 12:24 pm
If there's only one file in the filegroup, then there's your answer. If there's more than one file in the filegroup, the data will be spread across all the files in the filegroup.
p.s. Don't use sysfiles, it's deprecated, included only for backward compatibility with SQL 2000. Use sys.database_files
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2011 at 12:27 pm
That is correct. But that isn't the issue.
How do I determine which filegroup a table is in using DMVs?
Here is what I have for indexes:
selectdistinct t.name 'TableName',
i.name 'IndexName',
'Index' 'Type',
fg.name 'FileGroupName',
f.name 'FileName',
left(f.filename, 1) 'FileDirectory',
f.filename 'FilePath'
fromsys.indexes i
inner join sys.tables t on t.object_id = i.object_id
inner join sys.filegroups fg on i.data_space_id = fg.data_space_id
inner join sys.sysfiles f on f.groupid = fg.data_space_id
orderby t.name, i.name
How do I get this for tables?
...
December 13, 2011 at 12:32 pm
isn't the "table" data stored wherever the HEAP or CLUSTERED idnex type is stored?
so you can infer that wherever the i.type_desc in(HEAP,CLUSTERED), that's where the table is.
i added one column here:
select distinct t.name 'TableName',
i.name 'IndexName',
'Index' 'Type',
i.type_desc,
fg.name 'FileGroupName',
f.name 'FileName',
left(f.filename, 1) 'FileDirectory',
f.filename 'FilePath'
from sys.indexes i
inner join sys.tables t on t.object_id = i.object_id
inner join sys.filegroups fg on i.data_space_id = fg.data_space_id
inner join sys.sysfiles f on f.groupid = fg.data_space_id
order by t.name, i.name
Lowell
December 13, 2011 at 12:35 pm
Lowell (12/13/2011)
isn't the "table" data stored wherever the HEAP or CLUSTERED idnex type is stored?
Yup.
The heap (index id 0) or clustered index (index id 1) is the table, it's not a separate structure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2011 at 12:47 pm
I thought so. But the data that was being returned wasn't really reflecting that. So instead of questioning the design, I questioned what I thought I knew.
Unfortunately, the person who designed the database didn't know this so I have some REALLY huge index files and small data files because all of the data is in the index files.
Based on the comments, here is the final query:
SELECTDISTINCT t.name 'TableName',
i.name 'IndexName',
(CASE WHEN i.type_desc IN ('HEAP', 'CLUSTERED') THEN 'DATA'
WHEN i.type_desc = 'NONCLUSTERED' THEN 'INDEX'
ELSE i.type_desc END) 'Type',
fg.name 'FileGroupName',
f.name 'FileName',
LEFT(f.physical_name, 1) 'FileDirectory',
f.physical_name 'FilePath'
FROMsys.indexes i
INNER JOIN sys.tables t ON t.object_id = i.object_id
INNER JOIN sys.filegroups fg ON i.data_space_id = fg.data_space_id
INNER JOIN sys.database_files f ON f.data_space_id = fg.data_space_id
ORDERBY t.name, i.name
Thank you!
...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply