Problem Statement: Script to find the duplicate stats.
Solution: SQL server allows to create duplicate statistics but having duplicate statistics are overkill to system.Use following script to find the duplicate statistics.
;WITH all_stats
AS (SELECT s.object_id,
s.NAME AS stats_name,
s.stats_id,
s.has_filter,
s.auto_created,
sc.stats_column_id,
sc.column_id,
c.NAME AS column_name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id
AND s.object_id = sc.object_id
AND sc.stats_column_id = 1
INNER JOIN sys.columns AS c
ON c.object_id = s.object_id
AND c.object_id = sc.object_id
AND c.column_id = sc.column_id
WHERE objectproperty(s.object_id, ‘IsUserTable’) = 1)
SELECT row_number()
OVER(
partition BY s1.object_id, s1.column_name
ORDER BY s1.column_name) AS stats_no,
Object_name(s1.object_id) AS table_name,
s1.stats_name AS stats_name,
s2.stats_name AS identical_stats_name,
s2.column_name
FROM all_stats AS s1
INNER JOIN all_stats AS s2
ON s1.object_id = s2.object_id
AND s1.stats_id != s2.stats_id
AND s1.stats_column_id = s2.stats_column_id
AND s1.column_id = s2.column_id
AND s1.has_filter = s2.has_filter
AND s1.auto_created != s2.auto_created
