Here's a cool script I came accross - similar to SHOWCONTIG, but without being all invasive and locking your tables
select *
into dba..contig from sys.dm_db_index_physical_stats(0, null,null,null,null)
select
(select name from sys.filegroups fg where fg.data_space_id = si.data_space_id) as Filegroup,
OBJECT_NAME(c.object_id) as tablename,
si.name as indexname,
c.index_type_desc as Type,
c.avg_fragmentation_in_percent as FragPC,
(c.page_count * 8.0) / 1024.0 /1024.0 as GB,
page_count
from dba..contig c
join sys.indexes si
on si.object_id = c.object_id and si.index_id = c.index_id
where page_count > 1
order by 1, page_count desc