Technical Article

IndexInformation

,

Retrieve Index Information for all tables.

select
db_name(db_id()) as DatabaseName
, schema_name(t.schema_id) as SchemaName
, t.name as TableName
, i.name as IndexName
, i.type_desc as IndexType
, c.name as ColumnName
, typ.name as DataType
--, c.column_id
--, ic.key_ordinal
, ic.is_included_column
, c.is_identity
, i.is_primary_key
, i.is_unique_constraint
, ic.is_descending_key
, i.is_unique
, i.fill_factor
from sys.tables t
inner join sys.indexes i on t.object_id = i.object_id 
inner join sys.index_columns ic on i.object_id = ic.object_id
and i.index_id = ic.index_id
inner join sys.columns c on ic.object_id = c.object_id
and ic.column_id = c.column_id
inner join sys.types typ on c.user_type_id = typ.user_type_id
where 1=1
and t.is_ms_shipped = 0
and i.is_hypothetical = 0
order by DatabaseName, SchemaName, TableName, IndexName, ic.is_included_column, ic.key_ordinal

Rate

1 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (2)

You rated this post out of 5. Change rating