DROP FUNCTION dbo.udf_GetUnusedIndexes
GO

CREATE FUNCTION dbo.udf_GetUnusedIndexes ()
RETURNS @udf_UnusedIndexes TABLE
   (
       DatabaseName            SYSNAME NULL,
       TableName               SYSNAME NULL,
       IndexName               SYSNAME NULL,
       IndexUserUpdates        INT
   )
AS
/***************************************************************
Purpose:   To retrieve all indexes in the current database that are not used.

           The following DMVs are used:
               sys.indexes                 - to retrieve the full list of indexes for the database
               sys.dm_db_index_usage_stats - to examine the usage stats for indexes that
                                             have been used/updated
                    
          The criteria used to determine the 'unused' status are:
          
               1.  Index has no entry in the sys.dm_db_index_usage_stats view - not been used or updated
               2.  Index has an entry in the sys.dm_db_index_usage_stats view - but has a value for user_updates but zero values
                   for user scans/seeks/lookups.
          
          
Author:        ChillyDBA
History:   27 Jun 2012 - Initial Issue

****************************************************************/

BEGIN

   INSERT @udf_UnusedIndexes
       (
           DatabaseName,
           TableName,
           IndexName,
           IndexUserUpdates
       )

   SELECT
       DB_NAME()                       AS DatabaseName,
       OBJECT_NAME(i.OBJECT_ID)        AS TableName ,
       i.name                          AS IndexName,
       s.user_updates                  AS IndexUserUpdates
        
       -- these columns were included in thee original query, but are obsolete as
       -- the join predicates ensure they will always be null
       --s.user_seeks,
       --s.user_scans,
       --s.user_lookups
      
   FROM sys.indexes i  
   LEFT JOIN sys.dm_db_index_usage_stats s
       ON s.OBJECT_ID = i.OBJECT_ID
       AND i.index_id = s.index_id
       AND s.database_id = DB_ID()
   WHERE OBJECTPROPERTY(i.OBJECT_ID, 'IsIndexable') = 1

   -- index_usage_stats has no reference to this index (not being used)
   AND s.index_id IS NULL

   -- index is being updated, but not used by seeks/scans/lookups
   OR (
           s.user_updates > 0
           AND s.user_seeks = 0
           AND s.user_scans = 0
           AND s.user_lookups = 0
       )
   ORDER BY OBJECT_NAME(i.OBJECT_ID) ASC  
  
   RETURN
END

SELECT * FROM dbo.udf_GetUnusedIndexes()