Here's the useful join from a number of Index related DMVs - it grabs the top 50 possible missing indexes ordered by priority

select top 50 priority = avg_total_user_cost * avg_user_impact *(user_seeks + user_scans)

,d.statement

,d.equality_columns

,d.inequality_columns

,d.included_columns

,s.avg_total_user_cost

,s.avg_user_impact

,s.user_seeks, s.user_scans

from sys.dm_db_missing_index_group_stats s

join sys.dm_db_missing_index_groups g

on s.group_handle=g.index_group_handle

join sys.dm_db_missing_index_details d

on g.index_handle = d.index_handle

where s.user_seeks > 3000

order by priority desc

--Below integrated Above and adding the Create Index Missing_ concatenated...tweak to your respective system (note the obvious Missing Label to them, makes it easy to drop them for upgrades, since the SCOM product group will not support these, you are at your own risk)

SELECT 'CREATE INDEX missing_index_' + CONVERT (varchar, a.index_group_handle) + '_' + CONVERT (varchar, c.index_handle) + ' ON ' + c.statement + ' (' + ISNULL (c.equality_columns,'') + CASE WHEN c.equality_columns IS NOT NULL AND c.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (c.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + c.included_columns + ')', '') AS create_index_statement ,CONVERT (DECIMAL (28,1), b.avg_total_user_cost * b.avg_user_impact * (b.user_seeks + b.user_scans) ) AS improvement_measurement, b.unique_compiles, b.user_seeks, b.user_scans, avg_user_impact, avg_system_impact, last_user_seek, last_user_scan

FROM sys.dm_db_missing_index_groups a

 INNER JOIN sys.dm_db_missing_index_group_stats b

   ON b.group_handle = a.index_group_handle

   INNER JOIN sys.dm_db_missing_index_details c

    ON a.index_handle = c.index_handle

WHERE b.avg_user_impact > 50
-- originally 90 (MVP Kevin E. Kline's suggestion), but I dropped this to 30 to see
-- but on larger databases I changed it to 50

AND c.database_id = DB_ID()

AND b.user_seeks > 30
-- modify this as necessary, for a DW, I would do it as low as 30, for other systems,
-- I went as high as 3000 (caveat - test and check on your systems thoroughly!)

ORDER BY b.avg_total_user_cost * b.avg_user_impact * (b.user_seeks + b.user_scans) DESC

--- created on SCOM's OperationsManager (for example, not because DBAs have issues with SCOM... 🙂

Remember not to add too many indexes to tables that have many already, ESPECIALLY if that table will continue to have significant Inserts

The addition of these 'missing indexes' will improve performance for READ operations on the tables

A LOUD THANK YOU to Mohit Gupta, a Canadian SQL Premier Field Engineer, for collaboration on the following 🙂

USE [OperationsManager]

GO

CREATE INDEX missing_index_1820_1819 ON [OperationsManager].[dbo].[Relationship] ([LastModified]) INCLUDE ([RelationshipId], [SourceEntityId], [TargetEntityId], [RelationshipTypeId], [IsDeleted])

-- double check what was there already:

CREATE NONCLUSTERED INDEX [idx_BaseManagedEntity_TopLevelHostEntityId] ON [dbo].[BaseManagedEntity]

(

        [BaseManagedEntityId] ASC,

        [TopLevelHostEntityId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

GO

-- fine, np

CREATE INDEX missing_index_55_54 ON [OperationsManager].[dbo].[BaseManagedEntity] ([BaseManagedTypeId], [IsDeleted]) INCLUDE ([BaseManagedEntityId], [DisplayName])

CREATE INDEX missing_index_24_23 ON [OperationsManager].[dbo].[Relationship] ([LastModified]) INCLUDE ([RelationshipId], [SourceEntityId], [TargetEntityId], [RelationshipTypeId], [IsDeleted], [TimeAdded])

-- double check what was there already:

USE [OperationsManager]

GO

ALTER TABLE [dbo].[TypedManagedEntity] ADD CONSTRAINT [idx_ManagedEntityManagedTypeId] UNIQUE NONCLUSTERED

(

        [BaseManagedEntityId] ASC,

        [ManagedTypeId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

GO

CREATE INDEX missing_index_1830_1829 ON [OperationsManager].[dbo].[TypedManagedEntity] ([LastModified]) INCLUDE ([BaseManagedEntityId], [ManagedTypeId], [IsDeleted])

CREATE INDEX missing_index_1604_1603 ON [OperationsManager].[dbo].[Monitor] ([TargetManagedEntityType]) INCLUDE ([MonitorId], [ParentMonitorId], [IsDependencyMonitor], [MonitorName], [RelationshipTypeId], [MemberMonitorId])

CREATE INDEX missing_index_1460_1459 ON [OperationsManager].[dbo].[Report] ([ReportTarget])

CREATE INDEX missing_index_1452_1451 ON [OperationsManager].[dbo].[Views] ([ViewTypeId]) INCLUDE ([ViewId])

CREATE INDEX missing_index_117_116 ON [OperationsManager].[dbo].[Report] ([ManagementPackId])

--- then had to drop missing_index_1820_1819 because it was a mistake (I also learned along the way to NAME the MISSING indexes added Better, see below

-- Only a clustered index can be dropped online btw 🙂

USE [OperationsManager]

GO

DROP INDEX [missing_index_1820_1819] ON [dbo].[Relationship] WITH ( ONLINE = OFF )

GO

--- For OperationsManagerDW

USE [OperationsManagerDW]

GO

/****** Object: Index [UN_RelationshipManagementGroup_RelationshipRowIdFromDateTime] Script Date: 11/30/2011 15:56:35 ******/

ALTER TABLE [dbo].[RelationshipManagementGroup] ADD CONSTRAINT [UN_RelationshipManagementGroup_RelationshipRowIdFromDateTime] UNIQUE NONCLUSTERED

(

        [RelationshipRowId] ASC,

        [FromDateTime] ASC

)

INCLUDE ([ToDateTime]),

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

CREATE INDEX missing_index_3655_3654 ON [OperationsManagerDW].[dbo].[RelationshipManagementGroup] ([FromDateTime]) INCLUDE ([RelationshipRowId], [ToDateTime])

-- do not create, already existing

USE [OperationsManagerDW]

GO

/****** Object: Index [IDX_MaintenanceMode_ManagedEntityRowIdStartDateTime] Script Date: 12/01/2011 17:30:02 ******/

CREATE UNIQUE NONCLUSTERED INDEX [IDX_MaintenanceMode_ManagedEntityRowIdStartDateTime] ON [dbo].[MaintenanceMode]

(

        [ManagedEntityRowId] ASC,

        [StartDateTime] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

GO

USE [OperationsManagerDW]

GO

/****** Object: Index [PK_MaintenanceMode] Script Date: 12/01/2011 17:30:25 ******/

ALTER TABLE [dbo].[MaintenanceMode] ADD CONSTRAINT [PK_MaintenanceMode] PRIMARY KEY CLUSTERED

(

        [MaintenanceModeRowId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

GO

-- no related indexes, so created.

CREATE INDEX missing_index_136_135 ON [OperationsManagerDW].[dbo].[MaintenanceMode] ([StartDateTime]) INCLUDE ([ManagedEntityRowId], [EndDateTime], [PlannedMaintenanceInd])

--

USE [OperationsManagerDW]

GO

ALTER TABLE [dbo].[ManagedEntityType] ADD CONSTRAINT [PK_ManagedEntityType] PRIMARY KEY CLUSTERED

(

        [ManagedEntityTypeRowId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ManagedEntityType] ADD CONSTRAINT [UN_ManagedEntityType_ManagedEntityTypeGuid] UNIQUE NONCLUSTERED

(

        [ManagedEntityTypeGuid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

GO

-- no related indexes, so created.

CREATE INDEX missing_index_1848_1847 ON [OperationsManagerDW].[dbo].[ManagedEntityType] ([ManagedEntityTypeSystemName])

-- check existing

USE [OperationsManagerDW]

GO

/****** Object: Index [IX_AlertStage_AlertGuidDBLastModifiedDateTimeResolutionStateAlertStageRowId] Script Date: 12/01/2011 17:33:17 ******/

CREATE NONCLUSTERED INDEX [IX_AlertStage_AlertGuidDBLastModifiedDateTimeResolutionStateAlertStageRowId] ON [Alert].[AlertStage]

(

        [AlertGuid] ASC,

        [DBLastModifiedDateTime] ASC,

        [ResolutionState] ASC,

        [AlertStageRowId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

GO

-- tiny table, not worth it.

CREATE INDEX missing_index_1860_1859 ON [OperationsManagerDW].[Alert].[AlertStage] ([MonitorAlertInd], [WorkflowRowId])

CREATE INDEX missing_index_1929_1928 ON [OperationsManagerDW].[Alert].[AlertStage] ([AlertRowId])

CREATE INDEX missing_index_1880_1879 ON [OperationsManagerDW].[Alert].[AlertStage] ([AlertRowId]) INCLUDE ([RaisedDateTime])

-- - smaller table, checking anyway

USE [OperationsManagerDW]

GO

CREATE NONCLUSTERED INDEX [IX_MonitorManagementPackVersion_ManagementPackVersionRowId] ON [dbo].[MonitorManagementPackVersion]

(

        [ManagementPackVersionRowId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

GO

-- did not bother

CREATE INDEX missing_index_1834_1833 ON [OperationsManagerDW].[dbo].[MonitorManagementPackVersion] ([RelationshipTypeRowId])

-- check current indexes on that table for overlap, size is 49k rows, so worth it for size

USE [OperationsManagerDW]

GO

CREATE NONCLUSTERED INDEX [IX_EventCategory_LastReceivedDateTime] ON [dbo].[EventCategory]

(

        [LastReceivedDateTime] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

USE [OperationsManagerDW]

GO

ALTER TABLE [dbo].[EventCategory] ADD CONSTRAINT [PK_EventCategory] PRIMARY KEY CLUSTERED

(

        [EventCategoryRowId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

GO

--created because it was usefull

CREATE INDEX missing_index_2_EventCategoryID_LastRecDateTime ON [OperationsManagerDW].[dbo].[EventCategory] ([EventCategoryId],[LastReceivedDateTime]) INCLUDE ([EventCategoryRowId], [EventPublisherRowId])

--check existing and table size (1000 rows, not worth it, but did it anyway)

CREATE INDEX missing_index_131_RelationshipTypeSystemName ON [OperationsManagerDW].[dbo].[RelationshipType] ([RelationshipTypeSystemName])

-- 2k in table, and none of the current covering

USE [OperationsManagerDW]

GO

CREATE NONCLUSTERED INDEX [IX_DiscoveryManagementPackVersion_ManagementPackVersionRowId] ON [dbo].[DiscoveryManagementPackVersion]

(

        [ManagementPackVersionRowId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DiscoveryManagementPackVersion] ADD CONSTRAINT [PK_DiscoveryManagementPackVersion] PRIMARY KEY CLUSTERED

(

        [DiscoveryManagementPackVersionRowId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

GO

USE [OperationsManagerDW]

GO

ALTER TABLE [dbo].[DiscoveryManagementPackVersion] ADD CONSTRAINT [UN_DiscoveryManagementPackVersion_DiscoveryManagementPack] UNIQUE NONCLUSTERED

(

        [DiscoveryRowId] ASC,

        [ManagementPackVersionRowId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

GO

-- created because worth it.

CREATE INDEX missing_index_WorkflowCategoryRowId ON [OperationsManagerDW].[dbo].[DiscoveryManagementPackVersion] ([WorkflowCategoryRowId])

-- Alert Stage has barely any rows, so moving on...

CREATE INDEX missing_index_28205_28204 ON [OperationsManagerDW].[Alert].[AlertStage] ([InsertReadyInd]) INCLUDE ([DatasetId], [ManagementGroupGuid], [AlertGuid], [AlertProblemGuid], [ManagedEntityGuid], [AlertName], [AlertDescription], [Severity], [Priority], [Category], [MonitorAlertInd], [WorkflowGuid], [RaisedDateTime], [CreatedDateTime], [ResolutionState], [Owner], [TicketId], [CustomField1], [CustomField2], [CustomField3], [CustomField4], [CustomField5], [CustomField6], [CustomField7], [CustomField8], [CustomField9], [CustomField10], [SiteName], [AlertParams], [ParameterHash], [RepeatCount], [AlertStringGuid], [DBLastModifiedDateTime], [DBLastModifiedByUserId], [AlertStageRowId], [AlertRowId], [TableGuid], [ManagedEntityRowId], [WorkflowRowId], [DWCreatedDateTime])

-- ditto this one is NOT worth creating either, SO MORAL OF THE STORY EVALUATE EACH ONE...

CREATE INDEX missing_index_1878_1877 ON [OperationsManagerDW].[Alert].[AlertStage] ([AlertRowId],[RaisedDateTime])

--- as a post validation, check through Jacob Buter's script as mentioned on Paul Neilsen's collaboration

--with fellow heavey-weight Itzik Ben-Gan

--- this will make sure you have not added Duplicate indexes 🙂 although they could have existed already

SET NOCOUNT ON

DECLARE @objname nvarchar(776)

     , @objid int -- the object id of the table

     ,@indid smallint -- the index id of an index

     , @groupid smallint -- the filegroup id of an index

     , @indname sysname

     , @groupname sysname

     , @status int

     , @keys nvarchar(2126) --Length (16*max_identifierLength)+(15*2)+(16*3)

     , @dbname sysname

     , @usrname sysname

     , @i int

     , @thiskey nvarchar(131) -- 128+3

-- Check to see that the object names are local to the current database.

SELECT @dbname = parsename(@objname,3)

IF @dbname IS NOT NULL

AND @dbname <> db_name()

BEGIN

 raiserror(15250,-1,-1)

END

-- create temp table

create table #indextable

(usr_name sysname

,table_name sysname

,index_name sysname collate database_default

,stats int

,groupname sysname collate database_default

,index_keys nvarchar(2126) collate database_default -- see @keys above for length descr

)

-- OPEN CURSOR OVER TABLES

DECLARE cur_tables CURSOR LOCAL STATIC

FOR

 SELECT t1.id

      , t1.name

      , t2.name

   FROM sysobjects t1

  INNER JOIN sysusers t2 on t1.uid = t2.uid

  WHERE type = 'U'

OPEN cur_tables

FETCH cur_tables

INTO @objid

   , @objname

   , @usrname

WHILE @@fetch_status >= 0

BEGIN

-- OPEN CURSOR OVER INDEXES

 DECLARE cur_indexes CURSOR LOCAL STATIC

 FOR

   SELECT indid

        , groupid

        , name

        , status

     FROM sysindexes

    WHERE id = @objid

      AND indid > 0

      AND indid < 255

      AND (status & 64) = 0

    ORDER BY indid

 OPEN cur_indexes

 FETCH cur_indexes

  INTO @indid

     , @groupid

     , @indname

     , @status

 WHILE @@fetch_status >= 0

 BEGIN

   -- First we'll figure out what the keys are.

   SELECT @keys = index_col(@usrname + '.' + @objname, @indid, 1)

        , @i = 2

   IF (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)

     SELECT @keys = @keys

                  + '(-)'

   SELECT @thiskey = index_col(@usrname + '.' + @objname, @indid, @i)

   IF ((@thiskey IS NOT NULL)

   AND (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))

     SELECT @thiskey = @thiskey

                     + '(-)'

   WHILE (@thiskey IS NOT NULL)

   BEGIN

     SELECT @keys = @keys

                  + ', '

                  + @thiskey

          , @i = @i + 1

     SELECT @thiskey = index_col(@usrname + '.' + @objname, @indid, @i)

     IF ((@thiskey IS NOT NULL)

     AND (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))

       SELECT @thiskey = @thiskey + '(-)'

   END

   SELECT @groupname = groupname

     FROM sysfilegroups

    WHERE groupid = @groupid

   -- INSERT ROW FOR INDEX

   INSERT INTO #indextable

          VALUES (@usrname, @objname, @indname, @status, @groupname, @keys)

   -- Next index

   FETCH cur_indexes

    INTO @indid

       , @groupid

       , @indname

       , @status

 END

 CLOSE cur_indexes

 DEALLOCATE cur_indexes

 FETCH cur_tables

  INTO @objid

     , @objname

     , @usrname

END

DEALLOCATE cur_tables

-- DISPLAY THE RESULTS DUPLICATED

SELECT @@SERVERNAME as Instance

    , DB_NAME() as DatabaseName

    , t1.usr_name as usr_name

    , t1.table_name as table_name

    , t1.index_name as index_name

    , convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group

              case

                when (t1.stats & 16)<>0

                  then 'clustered'

                  else 'nonclustered'

              end

            + case

                when (t1.stats & 1)<>0

                  then ', ignore duplicate keys'

                  else ''

              end

            + case

                when (t1.stats & 2)<>0

                  then ', unique'

                  else ''

              end

            + case

                when (t1.stats & 4)<>0

                  then ', ignore duplicate rows'

                  else ''

              end

            + case

                when (t1.stats & 64)<>0

                  then ', statistics'

                  else

                    case

                      when (t1.stats & 32)<>0

                        then ', hypothetical'

                        else ''

                    end

              end

            + case

                when (t1.stats & 2048)<>0

                  then ', primary_key'

                  else ''

              end

            + case

                when (t1.stats & 4096)<>0

                  then ', unique_key'

                  else ''

              end

           + case

               when (t1.stats & 8388608)<>0

                 then ', auto_create'

                 else ''

             end

           + case

               when (t1.stats & 16777216)<>0

                 then ', stats_no_recompute'

                 else ''

               end

           + ' located on '

           + t1.groupname) as index_description

    , t2.index_keys as index_keys

    , t2.index_name as index_name

    , convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group

              case

                when (t2.stats & 16)<>0

                  then 'clustered'

                  else 'nonclustered'

              end

            + case

                when (t2.stats & 1)<>0

                  then ', ignore duplicate keys'

                  else ''

              end

            + case

                when (t2.stats & 2)<>0

                  then ', unique'

                  else ''

              end

            + case

                when (t2.stats & 4)<>0

                  then ', ignore duplicate rows'

                  else ''

              end

            + case

                when (t2.stats & 64)<>0

                  then ', statistics'

                  else

                    case

                      when (t2.stats & 32)<>0

                        then ', hypothetical'

                        else ''

                    end

              end

            + case

                when (t2.stats & 2048)<>0

                  then ', primary_key'

                  else ''

              end

            + case

                when (t2.stats & 4096)<>0

                  then ', unique key'

                  else ''

              end

            + case

                when (t2.stats & 8388608)<>0

                  then ', auto create'

                  else ''

                end

            + case

                when (t2.stats & 16777216)<>0

                  then ', stats no recompute'

                  else ''

              end

            + ' located on '

            + t2.groupname) as index_description

    , t2.index_keys as index_keys

 FROM #indextable t1

INNER JOIN #indextable t2 ON t1.table_name = t2.table_name

                         AND t1.index_name != t2.index_name

                         AND t1.index_keys = t2.index_keys

ORDER BY t1.table_name

       , t1.index_name

-- DISPLAY THE RESULTS OVERLAPPING

SELECT @@SERVERNAME as Instance

    , DB_NAME() as DatabaseName

    , t1.usr_name as usr_name

    , t1.table_name as table_name

    , t1.index_name as index_name

    , convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group

              case

                when (t1.stats & 16)<>0

                  then 'clustered'

                  else 'nonclustered'

              end

            + case

                when (t1.stats & 1)<>0

                  then ', ignore duplicate keys'

                  else ''

              end

            + case

                when (t1.stats & 2)<>0

                  then ', unique'

                  else ''

              end

            + case

                when (t1.stats & 4)<>0

                  then ', ignore duplicate rows'

                  else ''

              end

            + case

                when (t1.stats & 64)<>0

                  then ', statistics'

                  else

                    case

                      when (t1.stats & 32)<>0

                        then ', hypothetical'

                        else ''

                    end

              end

            + case

                when (t1.stats & 2048)<>0

                  then ', primary_key'

                  else ''

              end

            + case

                when (t1.stats & 4096)<>0

                  then ', unique key'

                  else ''

              end

           + case

               when (t1.stats & 8388608)<>0

                 then ', auto create'

                 else ''

             end

           + case

               when (t1.stats & 16777216)<>0

                 then 'stats no recompute'

                 else ''

               end

           + ' located on '

           + t1.groupname) as index_description

    , t2.index_keys as index_keys

    , t2.index_name as index_name

    , convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group

              case

                when (t2.stats & 16)<>0

                  then 'clustered'

                  else 'nonclustered'

              end

            + case

                when (t2.stats & 1)<>0

                  then ', ignore duplicate keys'

                  else ''

              end

            + case

                when (t2.stats & 2)<>0

                  then ', unique'

                  else ''

              end

            + case

                when (t2.stats & 4)<>0

                  then ', ignore duplicate rows'

                  else ''

              end

            + case

                when (t2.stats & 64)<>0

                  then ', statistics'

                  else

                    case

                      when (t2.stats & 32)<>0

                        then ', hypothetical'

                        else ''

                    end

              end

            + case

                when (t2.stats & 2048)<>0

                  then ', primary_key'

                  else ''

              end

            + case

                when (t2.stats & 4096)<>0

                  then ', unique key'

                  else ''

              end

            + case

                when (t2.stats & 8388608)<>0

                  then ', auto create'

                  else ''

                end

            + case

                when (t2.stats & 16777216)<>0

                  then ', stats no recompute'

                  else ''

              end

            + ' located on '

            + t2.groupname) as index_description

    , t2.index_keys as index_keys

 FROM #indextable t1

INNER JOIN #indextable t2 ON t1.table_name = t2.table_name

                         AND t1.index_name != t2.index_name

                         AND t1.index_keys like t2.index_keys + ',' + '%'

                         AND LTRIM(RTRIM(t1.index_keys)) != LTRIM(RTRIM(t2.index_keys))

ORDER BY t1.table_name

       , t1.index_name

DROP TABLE #indextable

-- There are too many to review right now, so I shall save that for a future post 🙂