https://www.brentozar.com/blitz/hypothetical-indexes-index-tuning-wizard/
Eliminates stats that cannot be updated.
USE [dbname]
GO
DECLARE @tblDrops TABLE (
tbl nvarchar(100)
, Index_or_Statistics nvarchar(200)
, DropCmd nvarchar(500)
);
DECLARE @sqlDrop nvarchar(500);
WITH hi AS (
SELECT
QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table]
, QUOTENAME([i].[name]) AS [Index_or_Statistics]
, 1 AS [Type]
FROM sys.[indexes] AS [i]
JOIN sys.[objects] AS [o]
ON i.[object_id] = o.[object_id]
WHERE 1=1
AND INDEXPROPERTY(i.[object_id], i.[name], 'IsHypothetical') = 1
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1
UNION ALL
SELECT
QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table]
, QUOTENAME([i].[name]) AS [Index_or_Statistics]
, 2 AS [Type]
FROM sys.[stats] AS [s]
JOIN sys.[objects] AS [o]
ON [o].[object_id] = [s].[object_id]
WHERE [s].[user_created] = 0
AND [o].[name] LIKE '[_]dta[_]%'
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1
)
INSERT INTO @tblDrops
SELECT
[hi].[Table] ,
[hi].[Index_or_Statistics] ,
CASE [hi].[Type]
WHEN 1 THEN 'DROP INDEX ' + [hi].[Index_or_Statistics] + ' ON ' + [hi].[Table] + ';'
WHEN 2 THEN 'DROP STATISTICS ' + hi.[Table] + '.' + hi.[Index_or_Statistics] + ';'
ELSE 'DEAR GOD WHAT HAVE YOU DONE?'
END AS [T-SQL Drop Command]
FROM [hi]
SELECT DropCmd
FROM @tblDrops;
/*
--Uncomment to run the purge
DECLARE db_cursor CURSOR FOR
SELECT DropCmd
FROM @tblDrops;
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @sqlDrop
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@sqlDrop);
FETCH NEXT FROM db_cursor INTO @sqlDrop
END
CLOSE db_cursor
DEALLOCATE db_cursor
*/
