There were some tables without clustered index that, after some time, a clustered index was created on them.
I assume that with behaviour described up, the system will have statistics for table without a CI and statistics for table with a CI. I think the first statistic is unnecessary. How can I find duplicate statistics like this?
I try to check some system tables, but without success.
Expecting to find duplicate statistics for tables. And prepare code to delete the unnecessary statistics.
0
The following can help you which I got from here, replace table1 with the table name you want. (Query modified from the page for versions with CL < 120)
;WITH StatsColumns AS
(
SELECT
s.object_id,
s.stats_id,
s.name AS StatisticName,
s.auto_created,
s.user_created,
c.name AS ColumnName,
sc.stats_column_id
FROM
sys.stats s
INNER JOIN
sys.stats_columns sc ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
INNER JOIN
sys.columns c ON sc.column_id = c.column_id AND sc.object_id = c.object_id
),
DuplicatedStats AS
(
SELECT
sc1.object_id,
sc1.ColumnName,
STUFF(
(SELECT ',' + T.StatisticName
FROM StatsColumns T
Where T.object_id = sc1.object_id and T.ColumnName = sc1.ColumnName
FOR XML PATH ('')), 1, 1, '') StatisticsNames,
COUNT(1) AS NumberOfDuplicatedStats
FROM
StatsColumns sc1
GROUP BY
sc1.object_id,
sc1.ColumnName
HAVING
COUNT(*) > 1
)
SELECT
OBJECT_NAME(ds.object_id) AS TableName,
ds.ColumnName,
ds.StatisticsNames,
ds.NumberOfDuplicatedStats
FROM
DuplicatedStats ds
WHERE
ds.object_id = OBJECT_ID('table1')
ORDER BY
TableName,
ds.ColumnName;
0