Script to find out missing index in SQL server database

 Below script is useful when you work with missing index in SQL server database, run below script on database it gives complete info where we have any missing indexes on the database, you need to change database name and values got get full data/fino,

select

*

, 'create index IX_'

+ replace(object_name(object_id, database_id), ' ', '_')

+ '_' + convert(varchar, mig.index_group_handle) + '_' + convert(varchar, mid.index_handle)

+ ' on ' + statement

+ ' (' + isnull (equality_columns,'') 

+ case when equality_columns is not null and inequality_columns is not null

then ','

else ''

end 

+ isnull (inequality_columns, '')

+ ')'

+ isnull(' include (' + included_columns + ')', '')

as create_index_statement

from

(

select

user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage

, migs.*

from

sys.dm_db_missing_index_group_stats migs

) as migs_adv

inner join sys.dm_db_missing_index_groups as mig

on migs_adv.group_handle = mig.index_group_handle

inner join sys.dm_db_missing_index_details as mid

on mig.index_handle = mid.index_handle

where

 database_id = DB_ID('msdb')

 and

index_advantage > 10000

order by

migs_adv.index_advantage desc

=======