July 16, 2014 at 7:15 am
Inherited large database with over 26 data files. Looking for a query to show what objects are on which files. I have one which list all the indexes are on which files, trying to found out what objects are on all the other files.
July 16, 2014 at 7:17 am
PS looking for objects on the logical file groups. I have a file group name called NIndex_1 but it has 18 logical files. Looking for what is on the logical files.
July 16, 2014 at 7:56 am
Try this
SELECT o.[name] object_name, o.[type], i.[name] index_name, i.[index_id], f.[name]
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
GO
Filegroup is logical where as file is physical.
July 16, 2014 at 8:04 am
but in this statement
select * from sys.sysfiles
I get all the logical/physical filenames, wanted to link this back to the objects to see what objects are at this level
July 17, 2014 at 2:09 am
If your filegroup has 18 physical files, then the objects on that filegroup are spread across all 18 physical files. So your table will not just live on 1 of the 18 files, it lives on all of them, so you cant say which file has which table as all 18 files have some of the data of that table.
SELECT
o.[name] AS ObjectName,
o.[type] AS ObjectType,
s.[name] AS SchemaName,
f.[name] AS [Filename],
i.[name] AS PrimaryKey,
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]
INNER JOIN
sys.schemas s
ON
s.[schema_id] = o.[schema_id]
order by
s.[name],
o.[name]
July 17, 2014 at 4:24 am
tcronin 95651 (7/16/2014)
Inherited large database with over 26 data files. Looking for a query to show what objects are on which files. I have one which list all the indexes are on which files, trying to found out what objects are on all the other files.
As already pointed out, objects (tables\indexes) are created across filegroups, not individual files.
This query will identify the table and its type
USE [yourdb]
select object_name(i.object_id),
CASE
WHEN i.name IS NULL THEN 'HEAP'
ELSE i.name
END
f.name
from sys.indexes i
inner join sys.objects o
on i.object_id = o.object_id
inner join sys.filegroups f
on i.data_space_id = f.data_space_id
where o.is_ms_shipped <> 1
order by object_name(i.object_id)
tcronin 95651 (7/16/2014)
PS looking for objects on the logical file groups. I have a file group name called NIndex_1 but it has 18 logical files. Looking for what is on the logical files.
see above for object filegroup placement
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply