Archive for December, 2011

My LinkedIn Profile

I have finally joined and here’s my profile:

Here’s a simple query that will list all indexes that have been created on the data filegroup, instead of the index filegroup(s).

SELECT distinct obj.[name] as [table name], obj.[type_desc] as [object type],
i.[name] as [index name], i.[index_id], f.[name] as [filegroup name], ((prt.used_page_count * 8 ) / 1024) as [Size (MB)]
FROM sys.indexes i, sys.filegroups f, sys.objects obj, sys.dm_db_partition_stats prt, sys.partitions p
where i.data_space_id = f.data_space_id and
i.object_id = obj.object_id and
p.object_id = i.object_id and
p.index_id = i.index_id and
p.object_id = prt.object_id and
p.index_id = prt.index_id and
p.partition_number = prt.partition_number and is not null and
i.type_desc in (‘HEAP’, ‘NONCLUSTERED’) and
obj.[type] in (‘U’, ‘V’) and
i.data_space_id = 1 — Filegroup
order by 1, 3

%d bloggers like this: