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.
 
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!
 

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s