SQL System View Query for table, index, index type and compression

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.
 o.[name] as TableName,
 i.[name] as IndexName,
 i.type_desc as IndexType,
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
 o.type = ‘U’ and — tables only
 o.[name] like ‘rolap%’
order by
Have fun!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s