Query index usage SQL

Select

Sch. Name +
'.' + T. Name
As [Table name],

I. Name as [index name],

I. type_desc,

Isnull (user_updates, 0)
As [total writes],

Isnull (user_seeks
+ User_scans + user_lookups, 0)
As [total reads],

S. last_user_seek,

S. last_user_scan
,

S. last_user_lookup,

Isnull (user_updates, 0)
-Isnull (user_seeks + user_scans
+ User_lookups), 0) as [difference],

P. reserved_page_count
* 8.0/1024 as spaceinmb

From SYS. Indexes
As I with (nolock)

Left outerjoin
SYS. dm_db_index_usage_statsas s
With (nolock)
On S. object_id
= I. object_id
And I. index_id
= S. index_id and S. database_id = db_id ()
And objectproperty (S. object_id, 'isusertable ')
= 1

Inner join
SYS. Tables
As T with (nolock)
On I. object_id
= T. object_id

Inner join
SYS. Schemas
As Sch
(Nolock)
On T. schema_id
= Sch. schema_id

Left outerjoin
SYS. dm_db_partition_stats
As P with (nolock)
On I. index_id
= P. index_id and I. object_id
= P. object_id

Where (1 = 1)

-- And isnull (user_updates, 0)> = isnull (user_seeks + user_scans + user_lookups), 0) -- shows all indexesincluding those that have not been used

-- And isnull (user_updates, 0)-isnull (user_seeks + user_scans + user_lookups), 0)> 0 -- only shows thoseindexes which have been used

-- And I. index_id> 1 -- only non-first indexes (I. e. non-primary key)

-- And I. is_primary_key <> 1 -- only those that are not defined ASA primary key)

-- And I. is_unique_constraint <> 1 -- only those that are not classed as "uniqueconstraints ".

Order by [Table name], [index name]