Comprehensive index information

[coughe]

I need to gather all the applicable options and their values that could have been used on an index during creation/alteration.

Does anyone know what system tables may hold the information concerning the following create index options:
1) ONLINE
2) MAXDOP
3) STATISTICS_NORECOMPUTE
4) DROP EXISTING

 

[ptheriault]

What version of SQL Server are you using?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[coughe]

I'm using SQL Server 2000 and 2005 (so the Online and Max degree of parallelism for 2005 only)

-Greg

 

[ptheriault]

Here are some helpful queries for 2005.

declare @dbid int
select @dbid = db_id()

select objectname=object_name(s.object_id), indexname=i.name, i.index_id
            , reads=range_scan_count + singleton_lookup_count
            , 'leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count
            , 'leaf_page_splits' = leaf_allocation_count
            , 'nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
            , 'nonleaf_page_splits' = nonleaf_allocation_count
from sys.dm_db_index_operational_stats (@dbid,NULL,NULL,NULL) s,
                        sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by reads desc, leaf_writes, nonleaf_writes

SELECT *
FROM sys.dm_db_index_usage_stats

SELECT * 
FROM sys.indexes

select objectname=object_name(s.object_id), indexname=i.name, i.index_id
                        ,reads=user_seeks + user_scans + user_lookups
                        ,writes =  user_updates
from sys.dm_db_index_usage_stats s,
            sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and s.object_id = i.object_id
and i.index_id = s.index_id
and s.database_id = @dbid
order by reads desc

For 2000.
you can just select * from sysindexes and sysindexkeys


- Paul

- If at first you don't succeed, find out if the loser gets anything.