As you start playing with the features of SQL Server 2008, you realized that you will want to view your table, what indexes you applied on to it, the type of index (heap, clustered, nonclustered, etc.) as well as some key features of tihe index including uniqueness, fill factor, rows, and what type of data_compression is applied to the index.  Here’s a quick query to do exactly this.

 

select 
 o.[name] as TableName,
 –i.object_id,
 i.[name] as IndexName,
 i.type_desc as IndexType,
 i.Is_Unique,
 i.fill_factor,
 i.[allow_row_locks],
 i.[allow_page_locks],
 p.[rows],
 p.data_compression_desc
from sys.objects (nolock) o
 left outer join sys.indexes (nolock) i
   on i.object_id = o.object_id
 left outer join sys.partitions (nolock) p
   on p.object_id = i.object_id
where
 o.type = ‘U’ and — tables only
 o.[name] like ‘rolap%’
order by
 o.[name]
 

Have fun!