SQLDBA-Un used index list

 USE master

GO

SET NOCOUNT ON

DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)

DECLARE @uptime NVARCHAR(500)

DECLARE @service_status NVARCHAR(500)

SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60

IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0

SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))

ELSE

SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60

SELECT @uptime = 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes' +CHAR(10)

IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')

BEGIN

SELECT @service_status = 'SQL Server is running but SQL Server Agent <<NOT>> running' +CHAR(10)

END

ELSE BEGIN

SELECT @service_status = 'SQL Server and SQL Server Agent both are running' +CHAR(10)

END

SELECT @uptime

UNION

SELECT @service_status

UNION

SELECT 'Remember!! results are skewed if the server recently rebooted / instance recently restarted!!'

UNION

SELECT 'Please do not blindly delete indexes based on the resultset below!!; this is merely informational!!'

Go

USE <DB Name>

GO

SELECT 

OBJECT_NAME(s.[object_id]) AS [ObjectName]

,i.name AS [IndexName]

,i.index_id

,s.user_updates AS [Writes]

,s.user_seeks + s.user_scans + s.user_lookups AS [Reads]

,i.type_desc AS [IndexType]

,i.fill_factor AS [FillFactor]

,i.has_filter

--,i.filter_definition

--,s.last_system_update

,s.last_user_update

,so.create_date AS 'table_create_date'

,(8 * SUM(au.used_pages)/1024) AS 'index_size_mb'

FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON s.[object_id] = i.[object_id]

INNER JOIN sys.objects so WITH (NOLOCK) 

ON s.[object_id] = so.[object_id]

INNER JOIN sys.partitions part WITH (NOLOCK) 

ON i.OBJECT_ID = part.OBJECT_ID 

AND i.index_id = part.index_id

INNER JOIN sys.allocation_units au WITH (NOLOCK) 

ON part.partition_id = au.container_id 

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

AND i.index_id = s.index_id

AND s.database_id = DB_ID()

AND (s.user_seeks + s.user_scans + s.user_lookups) = 0

AND i.type_desc = 'NONCLUSTERED'

GROUP BY

s.object_id

,i.name

,i.index_id

,s.user_updates

,s.user_seeks 

,s.user_scans 

,s.user_lookups

,i.type_desc

,i.fill_factor

,i.has_filter

--,i.filter_definition

--,s.last_system_update

,s.last_user_update

,so.create_date

--ORDER BY s.user_updates DESC OPTION (RECOMPILE);

--ORDER BY so.create_date DESC OPTION (RECOMPILE);

ORDER BY index_size_mb DESC OPTION (RECOMPILE);