TSQL to find list of all index not used for last 30 days

This code is designed to provide information about the indexes in the database, including their creation dates, last usage dates, and whether they have been used recently.

TSQL

-- Create a temporary table to store index information
CREATE TABLE #IndexInfo
(
    TableName NVARCHAR(128),
    IndexName NVARCHAR(128),
    CreationDate DATETIME,
    LastUsedDate DATETIME,
    NotUsedForLast30Days VARCHAR(3)
);

-- Populate the temporary table with index information
EXEC sp_MSforeachtable '
    INSERT INTO #IndexInfo (TableName, IndexName, CreationDate, LastUsedDate, NotUsedForLast30Days)
    SELECT
        OBJECT_NAME(i.object_id) AS TableName,
        i.name AS IndexName,
        o.create_date AS CreationDate,
        COALESCE(us.last_user_seek, us.last_user_scan, us.last_user_lookup, us.last_user_update) AS LastUsedDate,
        CASE
            WHEN COALESCE(us.last_user_seek, us.last_user_scan, us.last_user_lookup, us.last_user_update) IS NULL OR
                 COALESCE(us.last_user_seek, us.last_user_scan, us.last_user_lookup, us.last_user_update) < DATEADD(DAY, -30, GETDATE()) THEN ''Yes''
            ELSE ''No''
        END AS NotUsedForLast30Days
    FROM sys.indexes i
    INNER JOIN sys.objects o ON i.object_id = o.object_id
    LEFT JOIN sys.dm_db_index_usage_stats us ON i.object_id = us.object_id AND i.index_id = us.index_id
    WHERE i.object_id = OBJECT_ID(''?'')
';

-- Retrieve the index information
SELECT
    TableName,
    IndexName,
    CreationDate,
    LastUsedDate,
    NotUsedForLast30Days
FROM #IndexInfo
ORDER BY TableName, IndexName;

-- Clean up the temporary table
DROP TABLE #IndexInfo;

TSQL

Result