To find all the objects / tables / Indexes that exist in a Filegroup, use following query:
SELECT
f.[name] AS [FileGroupName]
, OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
, i.name AS [Index]
, p.rows AS [Row Count]
, i.type_desc AS [Index Type]
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.partitions p
ON i.object_id = p.object_id
AND p.index_id = i.index_id
WHERE o.type = 'U' -- User Created Tables
-- and f.name = '<filter for interested filegroup using name>'
order by p.rows
SELECT
f.[name] AS [FileGroupName]
, OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
, i.name AS [Index]
, p.rows AS [Row Count]
, i.type_desc AS [Index Type]
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.partitions p
ON i.object_id = p.object_id
AND p.index_id = i.index_id
WHERE o.type = 'U' -- User Created Tables
-- and f.name = '<filter for interested filegroup using name>'
order by p.rows
No comments:
Post a Comment