Duplicate Statistics Search and Destroy

---------------------------------------------------------------------------------------------------
--Name:StatisticsDuplicateDrop.sql
--Purpose:To find autocreated stats that have an overlapping statistic, and generate a script to drop them.
--Notes:Auto created stats have only one column.
--History:
--mm/dd/yyyyInitDescription
-------------------------------------------------------------------------------------------
--02/13/2020PLSCreated
---------------------------------------------------------------------------------------------------
--Make CTEs
---------------------------------------------------------------------------------------------------
WITH
cteAutoStats
AS
(
SELECT
s.object_id
,s.name
,sc.column_id
FROM sys.stats AS s
JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id
AND s.stats_id = sc.stats_id
WHERE 
s.auto_created = 1
AND sc.stats_column_id = 1
AND OBJECT_SCHEMA_NAME(s.object_id) != 'sys'
)
,cteUserStats
AS
(
SELECT
s.object_id
,s.name
,sc.column_id
FROM sys.stats AS s
JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id
AND s.stats_id = sc.stats_id
WHERE
s.auto_created = 0
AND sc.stats_column_id = 1
AND OBJECT_SCHEMA_NAME(s.object_id) != 'sys'
)

---------------------------------------------------------------------------------------------------
--Get results
---------------------------------------------------------------------------------------------------
SELECT DISTINCT
SchemaName = OBJECT_SCHEMA_NAME(cus.object_id)
,TableName = OBJECT_NAME(cus.object_id)
,ColumnName = c.name
,OverlappingStat = STUFF
(
(
SELECT '; ' + xmls.name
FROM cteUserStats AS xmls
WHERE
xmls.object_id = cus.object_id
AND xmls.column_id = cas.column_id
ORDER BY xmls.name
FOR XML PATH('')
)
,1
,1
,''
)
,DuplicateAutoStat = cas.name
,DropScript =
'USE ' + QUOTENAME(DB_NAME()) + '; DROP STATISTICS ' 
+ QUOTENAME(OBJECT_SCHEMA_NAME(cus.object_id)) 
+ '.' + QUOTENAME(OBJECT_NAME(cus.object_id)) 
+ '.' + QUOTENAME(cas.name)
FROM cteAutoStats AS cas
JOIN cteUserStats AS cus
ON cas.object_id = cus.object_id
AND cas.column_id = cus.column_id
JOIN sys.columns AS c
ON cus.object_id = c.object_id
AND cus.column_id = c.column_id
ORDER BY
SchemaName
,TableName
,ColumnName