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_descfrom 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_idwhere  o.type…

Rate this: