August 23, 2009 at 5:54 am
Hi,
I have a requirement where I have to generate all objects in a database in the following format.
objectname filename filegroup sizeofobject typeofobject
Thanks in Advance
August 23, 2009 at 6:09 am
You'll need sys.objects for the name and type, sys.partitions and sys.data_spaces for the filegroup (for tables only) and either sp_spaceused or sys.dm_db_index_physical_stats for the size (again, only for tables).
If there's only one file in a filegroup, you can get the filename from sys.database_files. If there are multiple files in a filegroup, I don't think there's a trivial way to see which an object is in, it will typically be in all
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
August 23, 2009 at 6:14 am
sys.data_spaces for the filegroup (for tables only) and either sp_spaceused or sys.dm_db_index_physical_stats for the size (again, only for tables)
Thanks for the info. But how do i find for indexes both clustered and non clustered indexes ( filegroup and index size)
August 23, 2009 at 6:27 am
newbie2sql (8/23/2009)
sys.data_spaces for the filegroup (for tables only) and either sp_spaceused or sys.dm_db_index_physical_stats for the size (again, only for tables)
Thanks for the info. But how do i find for indexes both clustered and non clustered indexes ( filegroup and index size)
sys.partitions and sys.data_spaces for the filegroup and sys.dm_db_index_physical_stats for the size.
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
August 23, 2009 at 3:26 pm
This script should get you started by telling you the object name, type, and which filegroup its a part of. The rest of the joins can be made accordingly with the different dmv's mentioned by Gail.
SELECTo.[name] [object name],
o.[type] [object type],
i.[name] [index name],
i.[index_id] [index id],
f.[name] [filegroup 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 = 2 --* New FileGroup*
where [o].[type] = 'U'
--and [o].[name] = 'OpRep_DMZ_Testing'
ORDER BY [filegroup name]
select * from sys.database_files --dataspace id = filegroup id
select * from sys.filegroups
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply