:setvar databasename TESA_3_23_0_SystemTest

use $(databasename)
go

SELECT TOP 100 'CREATE NONCLUSTERED INDEX IX1_' + object_name(c.object_id) + left(cast(newid() as varchar(500)),5) + char(10)
+ ' on ' + object_name(c.object_id)
+ '('
+ case when c.equality_columns is not null and c.inequality_columns is not null then c.equality_columns + ',' + c.inequality_columns
when c.equality_columns is not null and c.inequality_columns is null then c.equality_columns
when c.inequality_columns is not null then c.inequality_columns
end
+ ')' + char(10)
+ case when c.included_columns is not null then 'Include (' + c.included_columns + ')'
else ''
end as includes
FROM sys.dm_db_missing_index_group_stats a
inner join sys.dm_db_missing_index_groups b
on a.group_handle = b.index_group_handle
inner join sys.dm_db_missing_index_details c
on c.index_handle = b.index_handle
where db_name(database_id) = '$(databasename)'
and equality_columns is not null
ORDER BY a.avg_total_user_cost * a.avg_user_impact * (a.user_seeks + a.user_scans)DESC

--
-- Query to list required indexes
--
--use $(databasename)
--go
--SELECT left(cast(newid() as varchar(500)),5) as x, db_name(database_id),object_name(c.object_id), c.equality_columns, c.inequality_columns, c.included_columns
--FROM sys.dm_db_missing_index_group_stats a
--inner join sys.dm_db_missing_index_groups b
--on a.group_handle = b.index_group_handle
--inner join sys.dm_db_missing_index_details c
--on c.index_handle = b.index_handle
--where db_name(database_id) = '$(databasename)'
--ORDER BY a.avg_total_user_cost * a.avg_user_impact * (a.user_seeks + a.user_scans)DESC