SQL Server View index usage and build missing indexes

--View the index usage of the table
SELECT TOP 1000
O.name as table name
, I.name as index name
, i.index_id as index ID
, dm_ius.user_seeks as Search times
, Dm_ius.user_scans as Scan count
, dm_ius.user_lookups as lookup times
, dm_ius.user_updates as update number
, p.tablerows as table row count
, ' DROP INDEX ' + QUOTENAME (i.name)
+ ' on ' + QUOTENAME (s.name) + '. ' + QUOTENAME (object_name (Dm_ius. object_id)) as ' DELETE statement '
From Sys.dm_db_index_usage_stats Dm_ius
INNER JOIN sys.indexes i on i.index_id = dm_ius.index_id and Dm_ius. object_id = i.object_id
INNER JOIN sys.objects o on Dm_ius. object_id = o.object_id
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
INNER JOIN (SELECT SUM (p.rows) tablerows, p.index_id, p.object_id
From Sys.partitions p GROUP by p.index_id, p.object_id) p
On p.index_id = dm_ius.index_id and Dm_ius. object_id = p.object_id
WHERE objectproperty (Dm_ius. object_id, ' isusertable ') = 1
and dm_ius.database_id = db_id ()
--and I.type_desc = ' nonclustered '--this specifies the type of index, clustered index, or nonclustered index
and I.is_primary_key = 0
and I.is_unique_constraint = 0
and O.name= ' testtable '--the name of the table to be looked up
ORDER by (Dm_ius.user_seeks + Dm_ius.user_scans + dm_ius.user_lookups) ASC



--View missing indexes on tables in database and generate indexed statements
SELECT t4.name,t1. [statement],t1.object_id, T2.user_seeks, T2.user_scans,
T1.equality_columns, T1.inequality_columns,t1.included_columns,
Case
--when t1.equality_columns is null and CHARINDEX (', ', T1.inequality_columns) =0 and t1.included_columns is null
-Then ' Create UNIQUE nonclustered INDEX ix_ ' + replace ((replace (replace (t1.[ Statement], ' [', ' _ '), '] ', ' _ '), '. ', ' _ ') + ' _ ' + replace ((replace (replace (IsNull (t1.equality_columns, ' 1 '), ' [', ' _ ') ), '] ', ' _ '), '. ', ' _ ') + ' _ '
--+replace ((Replace (replace (IsNull (t1.inequality_columns, ' _2 '), ' [', ' _ ')), '] ', ' _ ')), '. ', ' _ ') + ' on ' + t1. [Statement] + ' (' + t1.inequality_columns + ' ASC) '
When--t1.equality_columns is null and CHARINDEX (', ', t1.inequality_columns) >0 and
T1.included_columns is null
Then ' create nonclustered INDEX ix_ ' + replace ((replace (replace ((replace (t1.[ Statement], ' [', ' _ '), '] ', ' _ '), '. ', ' _ ') '), ', ', ' _ ') + ' _ '
+replace (replace (replace (replace (IsNull (T1.equality_columns, ' 2 '), ' [', '), ' [', '), '. ', '), ', ', '), ', ' ‘)
+replace ((Replace (replace (replace (IsNull (T1.inequality_columns, ' 2 '), ' [', ') '), '] ', ') '), '. ', ') '), ', ', ' _ ') + ' on ' + T1. [Statement] + ' (' +
Case when T1.equality_columns was null then '
When Charindex (', ', t1.equality_columns) =0 then t1.equality_columns + ' ASC '
When Charindex (', ', t1.equality_columns) >0 then replace (t1.equality_columns, ', ', ' ASC, ') + ' ASC '
End
+
Case when T1.equality_columns was not null and CHARINDEX (', ', t1.inequality_columns) =0 then ', ' +t1.inequality_columns + ' ASC) '
When T1.equality_columns was null and CHARINDEX (', ', t1.inequality_columns) =0 then ' +t1.inequality_columns + ' ASC) '
When t1.inequality_columns was null then ') '
When Charindex (', ', t1.inequality_columns) > 0 Then ', ' + replace (t1.inequality_columns, ', ', ' ASC, ') + ' ASC ') '
When T1.equality_columns was null and CHARINDEX (', ', t1.inequality_columns) > 0 Then ' + replace (t1.inequality_columns, ', ', ' ASC, ') + ' ASC '
End
When T1.included_columns was not null
Then ' create nonclustered INDEX ix_ ' + replace ((replace (replace ((replace (t1.[ Statement], ' [', ' _ '), '] ', ' _ '), '. ', ' _ ') '), ', ', ' _ ') + ' _ '
+replace (replace (replace (replace (IsNull (T1.equality_columns, ' 2 '), ' [', '), ' [', '), '. ', '), ', ', '), ', ' ‘)
+replace ((Replace (replace (replace (IsNull (T1.inequality_columns, ' 2 '), ' [', '), ' [', ')), '] ', '), '. ', ' ), ', ', ' _ ') + ' on ' + t1. [Statement] + ' (' +
Case when T1.equality_columns was null then '
When Charindex (', ', t1.equality_columns) = 0 Then t1.equality_columns + ' ASC '
When Charindex (', ', t1.equality_columns) > 0 Then replace (t1.equality_columns, ', ', ' ASC, ') + ' ASC '
End
+
Case when T1.equality_columns was not null and CHARINDEX (', ', t1.inequality_columns) =0 then ', ' +t1.inequality_columns + ' ASC) '
When T1.equality_columns was null and CHARINDEX (', ', t1.inequality_columns) =0 then ' +t1.inequality_columns + ' ASC) '
When t1.inequality_columns was null then ') '
When T1.equality_columns was not null and CHARINDEX (', ', t1.inequality_columns) > 0 Then ', ' + replace (t1.inequality_col Umns, ', ', ' ASC, ') + ' ASC '
When T1.equality_columns was null and CHARINDEX (', ', t1.inequality_columns) > 0 Then ' + replace (t1.inequality_columns, ', ', ' ASC, ') + ' ASC '
End
+ ' INCLUDE (' + t1.included_columns + ') '

End as ' indexed statement '


From sys.dm_db_missing_index_groups as T3
Join Sys.dm_db_missing_index_details as T1
On t1.index_handle = T3.index_handle
Join Sys.dm_db_missing_index_group_stats as T2
On t2.group_handle = T3.index_group_handle
Join sys.databases as T4
On t1.database_id = t4.database_id
WHERE t1.database_id = db_id ()--and object_id = object_id (' interface. Merchant Device table ')
ORDER BY T2.user_seeks Desc

--t4.name,t1.object_id