SQL Server - Drop indexes and stats

 

Query to get the 'drop query for Stats'

SELECT distinct 'DROP STATISTICS ' + sca.name + '.' + t.name + '.' + s.name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c
ON sc.object_id = c.object_id AND c.column_id = sc.column_id
INNER JOIN sys.tables AS t
ON s.object_id = t.object_id
INNER JOIN sys.schemas As sca on t.schema_id = sca.schema_id
where s.name like '_dta_stat_%'

Query to get the 'drop query for Index'

SELECT distinct 'DROP INDEX ' + s.name + ' ON ' + sca.name + '.' + t.name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c
ON sc.object_id = c.object_id AND c.column_id = sc.column_id
INNER JOIN sys.tables AS t
ON s.object_id = t.object_id
INNER JOIN sys.schemas As sca on t.schema_id = sca.schema_id
where s.name like '_dta_index_%'