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;