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)

Share

Share

Rate

1 (2)