Index Usage Stats

IF OBJECT_ID('tempdb..#TMP1') IS NOT NULL
    DROP TABLE #TMP1
GO

IF OBJECT_ID('tempdb..#TMP2') IS NOT NULL
    DROP TABLE #TMP2
GO

USE NGPROD
GO

DECLARE @TblName SYSNAME = NULL;

SET @TblName = '<your table name here>'

IF @TblName IS NULL
SELECT '***** YOU MUST SPECIFY A TABLE NAME IN THE @TblName VARIABLE BEFORE RUNNING THIS SCRIPT *****!!!!!!!!'

IF @TblName IS NOT NULL
BEGIN
SELECT 
OBJECT_NAME(I.Object_id) AS TableName
,I.NAME AS IndexName
,' CREATE ' + CASE 
WHEN I.is_unique = 1
THEN ' UNIQUE '
ELSE ''
END + I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' + RTRIM(I.NAME) + ' ON ' + Schema_name(T.Schema_id) + '.' + T.NAME + '(' + KeyColumns + ') ' + ISNULL(' INCLUDE (' + IncludedColumns + ')', '') + ISNULL(' WHERE  ' + I.Filter_definition, '') + ' WITH ( ' + CASE 
WHEN I.is_padded = 1
THEN ' PAD_INDEX = ON '
ELSE ' PAD_INDEX = OFF '
END + ',' + 'FILLFACTOR = ' + CONVERT(CHAR(5), CASE 
WHEN I.Fill_factor = 0
THEN 100
ELSE I.Fill_factor
END) + ',' +
-- default value 
'SORT_IN_TEMPDB = OFF ' + ',' + CASE 
WHEN I.ignore_dup_key = 1
THEN ' IGNORE_DUP_KEY = ON '
ELSE ' IGNORE_DUP_KEY = OFF '
END + ',' + CASE 
WHEN ST.no_recompute = 0
THEN ' STATISTICS_NORECOMPUTE = OFF '
ELSE ' STATISTICS_NORECOMPUTE = ON '
END + ',' +
-- default value  
' DROP_EXISTING = ON ' + ',' +
-- default value  
' ONLINE = OFF ' + ',' + CASE 
WHEN I.allow_row_locks = 1
THEN ' ALLOW_ROW_LOCKS = ON '
ELSE ' ALLOW_ROW_LOCKS = OFF '
END + ',' + CASE 
WHEN I.allow_page_locks = 1
THEN ' ALLOW_PAGE_LOCKS = ON '
ELSE ' ALLOW_PAGE_LOCKS = OFF '
END + ' ) ON [' + DS.NAME + ' ] ' [CreateIndexScript]
INTO #TMP1
FROM sys.indexes I
JOIN sys.tables T ON T.Object_id = I.Object_id
JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid
JOIN sys.dm_db_index_usage_stats dm_ius ON I.index_id = dm_ius.index_id
AND dm_ius.OBJECT_ID = I.OBJECT_ID
JOIN (
SELECT *
FROM (
SELECT IC2.object_id
,IC2.index_id
,STUFF((
SELECT ' , ' + C.NAME + CASE 
WHEN MAX(CONVERT(INT, IC1.is_descending_key)) = 1
THEN ' DESC '
ELSE ' ASC '
END
FROM sys.index_columns IC1
JOIN Sys.columns C ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column = 0
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY IC1.object_id
,C.NAME
,index_id
ORDER BY MAX(IC1.key_ordinal)
FOR XML PATH('')
), 1, 2, '') KeyColumns
FROM sys.index_columns IC2
--WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables  
GROUP BY IC2.object_id
,IC2.index_id
) tmp3
) tmp4 ON I.object_id = tmp4.object_id
AND I.Index_id = tmp4.index_id
JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id
JOIN sys.data_spaces DS ON I.data_space_id = DS.data_space_id
JOIN sys.filegroups FG ON I.data_space_id = FG.data_space_id
LEFT JOIN (
SELECT *
FROM (
SELECT IC2.object_id
,IC2.index_id
,STUFF((
SELECT ' , ' + C.NAME
FROM sys.index_columns IC1
JOIN Sys.columns C ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column = 1
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY IC1.object_id
,C.NAME
,index_id
FOR XML PATH('')
), 1, 2, '') IncludedColumns
FROM sys.index_columns IC2
GROUP BY IC2.object_id
,IC2.index_id
) tmp1
WHERE IncludedColumns IS NOT NULL
) tmp2 ON tmp2.object_id = I.object_id
AND tmp2.index_id = I.index_id
WHERE I.is_primary_key = 0
AND I.is_unique_constraint = 0
AND I.Object_id = object_id(@TblName) --Comment for all tables 


-- Unused indexes with create index DDL
SELECT
o.name AS ObjectName
, i.name AS IndexName
, STATS_DATE ( i.object_id , i.index_id ) as IndexCreatedDate
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, dm_ius.last_user_seek AS [Last User Seek]
, dm_ius.last_user_scan AS [Last User Scan]
, dm_ius.last_system_lookup AS [Last User Lookup]
, dm_ius.last_user_update AS [Last User Update]
, 'DROP INDEX ' + QUOTENAME(RTRIM(i.name))
+ ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
INTO #TMP2
FROM sys.dm_db_index_usage_stats dm_ius (NOLOCK)
INNER JOIN sys.indexes i (NOLOCK) ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o (NOLOCK) ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s (NOLOCK) ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p (NOLOCK) GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND o.name = @tblName
 AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
--ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

SELECT
ObjectName AS [Table Name],
b.IndexName AS [Index Name],
IndexCreatedDate AS [Index Create Date],
IndexID AS [Index ID],
UserSeek AS [User Seeks],
UserScans AS [User Scans],
UserLookups AS [User Lookups],
UserUpdates AS [User Updates],
TableRows AS [Table rows],
[Last User Seek],
[Last User Scan],
[Last User Lookup],
[Last User Update],
[drop statement],
b.CreateIndexScript AS [Create Index DDL]
FROM 
#TMP2 a INNER JOIN #TMP1 b ON a.indexname = b.indexname
ORDER BY
(UserSeek + UserScans+ UserLookups)
END;