/
FindDupIdx_Ch04_DuplicateIndexes.sql
191 lines (96 loc) · 3.32 KB
/
FindDupIdx_Ch04_DuplicateIndexes.sql
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
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
IF OBJECT_ID (N'tempdb.dbo.#GetDupIdx') IS NOT NULL
DROP TABLE #GetDupIdx
CREATE TABLE #GetDupIdx
(
[Database Name] varchar(500),
[Table Name] varchar(1000),
[Index Name] varchar(1000),
[Indexed Column Names] varchar(1000),
[Index Type] varchar(50),
[Unique] char(1),
[Primary Key] char(1),
[Page Count] int,
[Size (MB)] int,
[Fragment %] int
)
Declare @db_name sysname
DECLARE c_db_names CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN('tempdb') --can exclude other databases
OPEN c_db_names
FETCH c_db_names INTO @db_name
WHILE @@Fetch_Status = 0
BEGIN
EXEC('Use ' + @db_name + ';
WITH FindDupIdx AS
(
SELECT DISTINCT sys.objects.name AS [Table Name],
sys.indexes.name AS [Index Name],
sys.indexes.type_desc AS [Index Type],
Case sys.indexes.is_unique
When 1 then ''Y''
When 0 then ''N'' End AS [Unique],
Case sys.indexes.is_primary_key
When 1 then ''Y''
When 0 then ''N'' End AS [Primary Key],
SUBSTRING((SELECT '', '' + sys.columns.Name as [text()]
FROM sys.columns
INNER JOIN sys.index_columns
ON sys.index_columns.column_id = sys.columns.column_id
AND sys.index_columns.object_id = sys.columns.object_id
WHERE sys.index_columns.index_id = sys.indexes.index_id
AND sys.index_columns.object_id = sys.indexes.object_id
AND sys.index_columns.is_included_column = 0
ORDER BY sys.columns.name
FOR XML Path('''')), 2, 10000) AS [Indexed Column Names],
ISNULL(SUBSTRING((SELECT '', '' + sys.columns.Name as [text()]
FROM sys.columns
INNER JOIN sys.index_columns
ON sys.index_columns.column_id = sys.columns.column_id
AND sys.index_columns.object_id = sys.columns.object_id
WHERE sys.index_columns.index_id = sys.indexes.index_id
AND sys.index_columns.object_id = sys.indexes.object_id
AND sys.index_columns.is_included_column = 1
ORDER BY sys.columns.name
FOR XML Path('''')), 2, 10000), '''') AS [Included Column Names],
sys.indexes.index_id, sys.indexes.object_id
FROM sys.indexes
INNER JOIN SYS.index_columns
ON sys.indexes.index_id = SYS.index_columns.index_id
AND sys.indexes.object_id = sys.index_columns.object_id
INNER JOIN sys.objects
ON sys.OBJECTS.object_id = SYS.indexes.object_id
WHERE sys.objects.type = ''U''
)
INSERT INTO #GetDupIdx (
[Database Name],[Table Name],[Index Name],[Indexed Column Names],
[Index Type],[Unique], [Primary Key],
[Page Count],[Size (MB)],[Fragment %]
)
SELECT DB_NAME(),FindDupIdx.[Table Name],
FindDupIdx.[Index Name],
FindDupIdx.[Indexed Column Names],
FindDupIdx.[Index Type],
FindDupIdx.[Unique],
FindDupIdx.[Primary Key],
PhysicalStats.page_count as [Page Count],
CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Size (MB)],
CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragment %]
FROM FindDupIdx
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
AS PhysicalStats
ON PhysicalStats.index_id = FindDupIdx.index_id
AND PhysicalStats.object_id = FindDupIdx.object_id
WHERE (SELECT COUNT(*) as Computed
FROM FindDupIdx Summary2
WHERE Summary2.[Table Name] = FindDupIdx.[Table Name]
AND Summary2.[Indexed Column Names] = FindDupIdx.[Indexed Column Names]) > 1
AND FindDupIdx.[Index Type] <> ''XML''
ORDER BY [Table Name], [Index Name], [Indexed Column Names], [Included Column Names]
')
FETCH c_db_names INTO @db_name
END
CLOSE c_db_names
DEALLOCATE c_db_names
SELECT * FROM #GetDupIdx