List Table Indexes

-- -----------------------------------------------------------------------------
-- List Table Indexes
--
-- @table_name  varchar  The table name.
-- -----------------------------------------------------------------------------

declare @table_name varchar(20);
set @table_name = 'THE_TABLE_NAME';

select i.name,
    i.type_desc [type],
    is_unique [unique],
    is_primary_key [primary],
    STUFF((
            select ', ' + c.name
            from sys.index_columns ic
            join sys.columns c on ic.column_id = c.column_id
                and ic.object_id = c.object_id
            where ic.object_id = i.object_id
                and ic.index_id = i.index_id
            order by ic.key_ordinal
            for xml PATH('')
            ), 1, 1, '') columns
from sys.indexes i
where i.object_id = object_id(@table_name);

-- -----------------------------------------------------------------------------
-- List Missing Table Indexs
-- -----------------------------------------------------------------------------

select DB_NAME(mid.database_id) [database],
    OBJECT_SCHEMA_NAME(mid.[object_id]) [schema],
    OBJECT_NAME(mid.[object_id]) [table],
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) as improvement_measure,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    unique_compiles,
    user_seeks,
    user_scans,
    avg_total_user_cost,
    avg_user_impact,
    system_seeks,
    system_scans,
    avg_total_system_cost,
    avg_system_impact
from sys.dm_db_missing_index_groups mig
inner join sys.dm_db_missing_index_group_stats migs on migs.group_handle = mig.index_group_handle
inner join sys.dm_db_missing_index_details mid on mig.index_handle = mid.index_handle
where migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
order by migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) desc;