Bonjour,
Pour trouver la liste des statistiques en double dans une base de données, j'ai écrit la requête suivante :
Qui me retourne bien les doublons, mais lorsque je regarde le résultat de DBCC SHOW_STATISTICS à l'aide de la requête suivante :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71 ;WITH CTE_STAT_COLUMNS AS ( SELECT T.name AS table_name , S.name AS stat_name , C.name AS column_name , SC.stats_column_id , CASE WHEN I.name IS NULL THEN 'S' ELSE 'I' END AS stat_type 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 SC.column_id = C.column_id INNER JOIN sys.tables AS T ON C.object_id = T.object_id LEFT JOIN sys.indexes AS I ON S.name = I.name ) , CTE_STAT_COLUMN_LIST AS ( SELECT table_name , stat_name , CAST(column_name AS varchar(max)) AS stat_column_list , stats_column_id , stat_type FROM CTE_STAT_COLUMNS WHERE stats_column_id = 1 UNION ALL SELECT SCL.table_name , SCL.stat_name , CAST(stat_column_list + ',' + SC.column_name AS varchar(max)) AS stat_column_list , SCL.stats_column_id + 1 , SC.stat_type FROM CTE_STAT_COLUMNS AS SC INNER JOIN CTE_STAT_COLUMN_LIST AS SCL ON SC.table_name = SCL.table_name AND SC.stat_name = SCL.stat_name AND SC.stats_column_id = SCL.stats_column_id + 1 ) , CTE_STAT_COLUMN_COUNT AS ( SELECT table_name , stat_name , MAX(stats_column_id) AS max_stats_column_id FROM CTE_STAT_COLUMN_LIST GROUP BY table_name, stat_name ) SELECT S.table_name , S.stat_name , I.stat_name AS index_name , S.stat_column_list , S.stats_column_id FROM CTE_STAT_COLUMN_LIST AS S INNER JOIN CTE_STAT_COLUMN_LIST AS I ON S.table_name = I.table_name AND S.stat_column_list = I.stat_column_list AND S.stat_type = 'S' AND I.stat_type = 'I' INNER JOIN CTE_STAT_COLUMN_COUNT AS SCCS ON S.table_name = SCCS.table_name AND S.stat_name = SCCS.stat_name AND S.stats_column_id = SCCS.max_stats_column_id INNER JOIN CTE_STAT_COLUMN_COUNT AS SCCI ON I.table_name = SCCI.table_name AND I.stat_name = SCCI.stat_name AND I.stats_column_id = SCCI.max_stats_column_id
J'obtiens pour RANGE_HI_KEY des valeurs différentes (plus exactement de types de données différents) dans les deux cas, lorsque les doublons trouvés sont sur des clés de plus d'une colonne.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 DBCC SHOW_STATISTICS (maTable, _WA_Sys_uneStatDeColonneDe_maTable) WITH HISTOGRAM DBCC SHOW_STATISTICS (maTable, uneStatDUnIndexDe_maTable) WITH HISTOGRAM
J'ai donc écrit la requête suivante :
Mais j'observe la même chose.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107 ;WITH CTE_STAT_COLUMNS AS ( SELECT T.name AS table_name , S.name AS stat_name , C.name AS column_name , SC.stats_column_id 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 SC.column_id = C.column_id INNER JOIN sys.tables AS T ON C.object_id = T.object_id LEFT JOIN sys.indexes AS I ON S.name = I.name WHERE I.name IS NULL ) , CTE_STAT_COLUMN_LIST AS ( SELECT table_name , stat_name , CAST(column_name AS varchar(max)) AS stat_column_list , stats_column_id FROM CTE_STAT_COLUMNS WHERE stats_column_id = 1 UNION ALL SELECT SCL.table_name , SCL.stat_name , CAST(stat_column_list + ',' + SC.column_name AS varchar(max)) AS stat_column_list , SCL.stats_column_id + 1 FROM CTE_STAT_COLUMNS AS SC INNER JOIN CTE_STAT_COLUMN_LIST AS SCL ON SC.table_name = SCL.table_name AND SC.stat_name = SCL.stat_name AND SC.stats_column_id = SCL.stats_column_id + 1 ) , CTE_STAT_COLUMN_COUNT AS ( SELECT table_name , stat_name , MAX(stats_column_id) AS max_stats_column_id FROM CTE_STAT_COLUMN_LIST GROUP BY table_name, stat_name ), CTE_INDEX_COLUMNS AS ( SELECT T.name AS table_name , I.name AS index_name , C.name AS column_name , IC.index_column_id FROM sys.indexes AS I INNER JOIN sys.index_columns AS IC ON IC.object_id = I.object_id AND IC.index_id = I.index_id INNER JOIN sys.columns AS C ON IC.object_id = C.object_id AND IC.column_id = C.column_id INNER JOIN sys.tables AS T ON I.object_id = T.object_id ) , CTE_INDEX_COLUMN_LIST AS ( SELECT table_name , index_name , CAST(column_name AS varchar(max)) AS index_column_list , index_column_id FROM CTE_INDEX_COLUMNS WHERE index_column_id = 1 UNION ALL SELECT ICL.table_name , ICL.index_name , CAST(index_column_list + ',' + IC.column_name AS varchar(max)) AS index_column_list , ICL.index_column_id + 1 FROM CTE_INDEX_COLUMNS AS IC INNER JOIN CTE_INDEX_COLUMN_LIST AS ICL ON IC.table_name = ICL.table_name AND IC.index_name = ICL.index_name AND IC.index_column_id = ICL.index_column_id + 1 ) , CTE_INDEX_COLUMN_COUNT AS ( SELECT table_name , index_name , MAX(index_column_id) AS max_index_column_id FROM CTE_INDEX_COLUMN_LIST GROUP BY table_name, index_name ) SELECT SCL.table_name , SCL.stat_name , ICL.index_name , SCL.stat_column_list , SCL.stats_column_id FROM CTE_STAT_COLUMN_LIST AS SCL INNER JOIN CTE_STAT_COLUMN_COUNT AS SCC ON SCL.table_name = SCC.table_name AND SCL.stat_name = SCC.stat_name AND SCL.stats_column_id = SCC.max_stats_column_id INNER JOIN CTE_INDEX_COLUMN_LIST AS ICL ON SCL.table_name = ICL.table_name AND SCL.stat_column_list = ICL.index_column_list INNER JOIN CTE_INDEX_COLUMN_COUNT AS ICC ON ICL.table_name = ICC.table_name AND ICL.index_name = ICC.index_name AND ICL.index_column_id = ICC.max_index_column_id
A quoi cela peut-il être dû ?
@++
Partager