· 9 years ago · Dec 04, 2016, 11:06 AM
1USE MAF_Tiger
2GO
3
4--CREATE PROCEDURE usp_RefactorIndexes
5--AS
6PRINT CONVERT(VARCHAR, GETDATE()) + ': Script started!'
7
8DECLARE @Index NVARCHAR(1024) = '',
9 @Catalog NVARCHAR(1024) = '',
10 @Schema NVARCHAR(1024) = '',
11 @Table NVARCHAR(1024) = '',
12 @CurrentTable NVARCHAR(1024) = '',
13 @Sql NVARCHAR(1024) = '',
14 @FullSql NVARCHAR(MAX) = '',
15 @ExecSql NVARCHAR(MAX),
16 @NewLineChar AS CHAR(2) = CHAR(13)
17
18PRINT 'Retrieving Spatial Indexes for deletion'
19
20SELECT t.name AS [Table],
21 ind.name AS [Index]
22INTO #DeleteIndex
23FROM
24 sys.spatial_indexes ind
25INNER JOIN
26 sys.tables t ON ind.object_id = t.object_id
27WHERE
28 ind.is_primary_key = 0
29ORDER BY
30 t.name, ind.name, ind.index_id
31
32SELECT @Table = A.[Table], @Index = A.[Index]
33FROM #DeleteIndex A
34
35WHILE @@ROWCOUNT <> 0
36BEGIN
37 SET ROWCOUNT 0
38
39 PRINT 'Dropping Index ''' + @Index + ''' on [' + @Table + ']'
40
41 SET @Sql = 'DROP INDEX ' + @Index + ' ON [' + @Table + ']'
42
43 EXEC(@sql)
44
45 DELETE FROM #DeleteIndex
46 WHERE [Table] = @Table
47 AND [Index] = @Index
48
49 SELECT @Table = A.[Table], @Index = A.[Index]
50 FROM #DeleteIndex A
51END
52
53PRINT 'Retrieving Indexes for deletion'
54
55INSERT INTO #DeleteIndex
56SELECT t.name AS [Table],
57 ind.name AS [Index]
58FROM
59 sys.indexes ind
60INNER JOIN
61 sys.tables t ON ind.object_id = t.object_id
62WHERE
63 ind.is_primary_key = 0
64ORDER BY
65 t.name, ind.name, ind.index_id
66
67SELECT @Table = A.[Table], @Index = A.[Index]
68FROM #DeleteIndex A
69
70WHILE @@ROWCOUNT <> 0
71BEGIN
72 SET ROWCOUNT 0
73
74 PRINT 'Dropping Index ''' + @Index + ''' on [' + @Table + ']'
75
76 SET @Sql = 'DROP INDEX ' + @Index + ' ON [' + @Table + ']'
77
78 EXEC(@sql)
79
80 DELETE FROM #DeleteIndex
81 WHERE [Table] = @Table
82 AND [Index] = @Index
83
84 SELECT @Table = A.[Table], @Index = A.[Index]
85 FROM #DeleteIndex A
86END
87
88DROP TABLE #DeleteIndex
89
90PRINT CONVERT(VARCHAR, GETDATE()) + ': Retrieving tables for processing!'
91
92SELECT *
93INTO #Tables
94FROM INFORMATION_SCHEMA.TABLES A
95WHERE 'geometry' IN (SELECT DATA_TYPE
96 FROM INFORMATION_SCHEMA.COLUMNS B
97 WHERE B.TABLE_CATALOG = A.TABLE_CATALOG
98 AND B.TABLE_SCHEMA = A.TABLE_SCHEMA
99 AND B.TABLE_NAME = A.TABLE_NAME)
100
101SELECT @Catalog = A.TABLE_CATALOG,
102 @Schema = A.TABLE_SCHEMA,
103 @Table = A.TABLE_NAME
104FROM #Tables A
105
106WHILE @@ROWCOUNT <> 0
107BEGIN
108 SET ROWCOUNT 0
109
110 PRINT CONVERT(VARCHAR, GETDATE()) + ': Processing Table [' + @Table + ']'
111
112 IF EXISTS (SELECT 1
113 FROM INFORMATION_SCHEMA.COLUMNS A
114 WHERE A.TABLE_CATALOG = @Catalog
115 AND A.TABLE_SCHEMA = @Schema
116 AND A.TABLE_NAME = @Table
117 AND A.COLUMN_NAME = 'PointGeog')
118 BEGIN
119 PRINT CONVERT(VARCHAR, GETDATE()) + ': Dropping PointGeog column from [' + @Table + ']'
120 SET @Sql = 'ALTER TABLE [' + @Table + '] DROP COLUMN PointGeog'
121 EXEC(@Sql)
122 END
123
124 IF EXISTS (SELECT 1
125 FROM INFORMATION_SCHEMA.COLUMNS A
126 WHERE A.TABLE_CATALOG = @Catalog
127 AND A.TABLE_SCHEMA = @Schema
128 AND A.TABLE_NAME = @Table
129 AND A.COLUMN_NAME = 'MinLat')
130 BEGIN
131 PRINT CONVERT(VARCHAR, GETDATE()) + ': Dropping MinLat column from [' + @Table + ']'
132 SET @Sql = 'ALTER TABLE [' + @Table + '] DROP COLUMN MinLat'
133 EXEC(@Sql)
134 END
135
136 IF EXISTS (SELECT 1
137 FROM INFORMATION_SCHEMA.COLUMNS A
138 WHERE A.TABLE_CATALOG = @Catalog
139 AND A.TABLE_SCHEMA = @Schema
140 AND A.TABLE_NAME = @Table
141 AND A.COLUMN_NAME = 'MinLong')
142 BEGIN
143 PRINT CONVERT(VARCHAR, GETDATE()) + ': Dropping MinLong column from [' + @Table + ']'
144 SET @Sql = 'ALTER TABLE [' + @Table + '] DROP COLUMN MinLong'
145 EXEC(@Sql)
146 END
147
148 IF EXISTS (SELECT 1
149 FROM INFORMATION_SCHEMA.COLUMNS A
150 WHERE A.TABLE_CATALOG = @Catalog
151 AND A.TABLE_SCHEMA = @Schema
152 AND A.TABLE_NAME = @Table
153 AND A.COLUMN_NAME = 'MaxLat')
154 BEGIN
155 PRINT CONVERT(VARCHAR, GETDATE()) + ': Dropping MaxLat column from [' + @Table + ']'
156 SET @Sql = 'ALTER TABLE [' + @Table + '] DROP COLUMN MaxLat'
157 EXEC(@Sql)
158 END
159
160 IF EXISTS (SELECT 1
161 FROM INFORMATION_SCHEMA.COLUMNS A
162 WHERE A.TABLE_CATALOG = @Catalog
163 AND A.TABLE_SCHEMA = @Schema
164 AND A.TABLE_NAME = @Table
165 AND A.COLUMN_NAME = 'MaxLong')
166 BEGIN
167 PRINT CONVERT(VARCHAR, GETDATE()) + ': Dropping MaxLong column from [' + @Table + ']'
168 SET @Sql = 'ALTER TABLE [' + @Table + '] DROP COLUMN MaxLong'
169 EXEC(@Sql)
170 END
171
172 DECLARE @GeomColumn VARCHAR(1024) = (SELECT TOP 1 A.COLUMN_NAME
173 FROM INFORMATION_SCHEMA.COLUMNS A
174 WHERE A.TABLE_CATALOG = @Catalog
175 AND A.TABLE_SCHEMA = @Schema
176 AND A.TABLE_NAME = @Table
177 AND A.DATA_TYPE = 'geometry'),
178 @LongColumn VARCHAR(1024),
179 @LatColumn VARCHAR(1024)
180
181 PRINT CONVERT(VARCHAR, GETDATE()) + ': Adding MinLong column to [' + @Table + ']'
182
183 SET @Sql = 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + ']' + @NewLineChar
184 + 'ADD [MinLong] AS [' + @GeomColumn + '].MakeValid().STEnvelope().STPointN((1)).STX PERSISTED NOT NULL'
185
186 EXEC(@Sql)
187
188 PRINT CONVERT(VARCHAR, GETDATE()) + ': MinLong column added to [' + @Table + ']'
189 PRINT CONVERT(VARCHAR, GETDATE()) + ': Adding MinLat column to [' + @Table + ']'
190
191 SET @Sql = 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + ']' + @NewLineChar
192 + 'ADD [MinLat] AS [' + @GeomColumn + '].MakeValid().STEnvelope().STPointN((1)).STY PERSISTED NOT NULL'
193
194 EXEC(@Sql)
195
196 PRINT CONVERT(VARCHAR, GETDATE()) + ': MinLat column added to [' + @Table + ']'
197 PRINT CONVERT(VARCHAR, GETDATE()) + ': Adding MaxLong column to [' + @Table + ']'
198
199 SET @Sql = 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + ']' + @NewLineChar
200 + 'ADD [MaxLong] AS [' + @GeomColumn + '].MakeValid().STEnvelope().STPointN((3)).STX PERSISTED NOT NULL'
201
202 EXEC(@Sql)
203
204 PRINT CONVERT(VARCHAR, GETDATE()) + ': MaxLong column added to [' + @Table + ']'
205 PRINT CONVERT(VARCHAR, GETDATE()) + ': Adding MaxLat column to [' + @Table + ']'
206
207 SET @Sql = 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + ']' + @NewLineChar
208 + 'ADD [MaxLat] AS [' + @GeomColumn + '].MakeValid().STEnvelope().STPointN((3)).STY PERSISTED NOT NULL'
209
210 EXEC(@Sql)
211
212 PRINT CONVERT(VARCHAR, GETDATE()) + ': MaxLat column added to [' + @Table + ']'
213
214 PRINT CONVERT(VARCHAR, GETDATE()) + ': Creating index on bounding box coordinates!'
215
216 SET @Sql = 'CREATE INDEX idx_' + @Table + '_MinLong_MinLat_MaxLong_MaxLat ON [' + @Table + ']([MinLong], [MinLat], [MaxLong], [MaxLat])'
217
218 EXEC(@Sql)
219
220 PRINT CONVERT(VARCHAR, GETDATE()) + ': Bounding Box index created!'
221
222 IF EXISTS (SELECT 1
223 FROM INFORMATION_SCHEMA.COLUMNS A
224 WHERE A.TABLE_CATALOG = @Catalog
225 AND A.TABLE_SCHEMA = @Schema
226 AND A.TABLE_NAME = @Table
227 AND A.COLUMN_NAME LIKE '%lat%')
228 BEGIN
229 IF EXISTS (SELECT 1
230 FROM INFORMATION_SCHEMA.COLUMNS A
231 WHERE A.TABLE_CATALOG = @Catalog
232 AND A.TABLE_SCHEMA = @Schema
233 AND A.TABLE_NAME = @Table
234 AND A.COLUMN_NAME LIKE '%intptlon%')
235 BEGIN
236 PRINT CONVERT(VARCHAR, GETDATE()) + ': Latitude and Longitude columns detected!'
237 PRINT CONVERT(VARCHAR, GETDATE()) + ': Creating PointGeog column on [' + @Table + ']'
238
239 SET @LongColumn = (SELECT TOP 1 A.COLUMN_NAME
240 FROM INFORMATION_SCHEMA.COLUMNS A
241 WHERE A.TABLE_CATALOG = @Catalog
242 AND A.TABLE_SCHEMA = @Schema
243 AND A.TABLE_NAME = @Table
244 AND A.COLUMN_NAME LIKE '%intptlon%')
245
246 SET @LatColumn = (SELECT TOP 1 A.COLUMN_NAME
247 FROM INFORMATION_SCHEMA.COLUMNS A
248 WHERE A.TABLE_CATALOG = @Catalog
249 AND A.TABLE_SCHEMA = @Schema
250 AND A.TABLE_NAME = @Table
251 AND A.COLUMN_NAME LIKE '%intptlat%')
252
253 PRINT CONVERT(VARCHAR, GETDATE()) + ': Converting latitude and longitude columns to non-nullable float values on [' + @Table + ']'
254
255 SET @Sql = 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + ']' + @NewLineChar
256 + 'ALTER COLUMN [' + @LongColumn + '] float NOT NULL' + @NewLineChar
257 + 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + ']' + @NewLineChar
258 + 'ALTER COLUMN [' + @LatColumn + '] float NOT NULL'
259
260 EXEC(@Sql)
261
262 PRINT CONVERT(VARCHAR, GETDATE()) + ': Creating index on latitude and longitude!'
263
264 SET @Sql = 'CREATE INDEX idx_' + @Table + '_' + @LongColumn + '_' + @LatColumn + ' ON [' + @Table + ']([' + @LongColumn + '], [' + @LatColumn + '])'
265
266 EXEC(@Sql)
267
268 PRINT CONVERT(VARCHAR, GETDATE()) + ': Latitude and Longitude index created!'
269
270 SET @Sql = 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + '] ADD [PointGeog] AS GEOMETRY::Point([' + @LongColumn + '], [' + @LatColumn + '], [' + @GeomColumn + '].STSrid) PERSISTED NOT NULL'
271
272 EXEC(@Sql)
273
274 PRINT CONVERT(VARCHAR, GETDATE()) + ': PointGeog column created.'
275 PRINT CONVERT(VARCHAR, GETDATE()) + ': Creating Spatial index on column!'
276
277 SET @Sql = 'DECLARE @minlat FLOAT, @minlong FLOAT, @maxlat FLOAT, @maxlong FLOAT, @Sql VARCHAR(MAX)' + @NewLineChar
278 + 'SELECT @minlat = MIN([' + @LatColumn + ']), @minlong = MIN([' + @LongColumn + ']), @maxlat = MAX([' + @LatColumn + ']), @maxlong = MAX([' + @LongColumn + ']) FROM [' + @Catalog + '].[' + @Schema + '].[' + @Table + '] A' + @NewLineChar
279 + 'SET @Sql = ''CREATE SPATIAL INDEX idx_' + @Table + '_PointGeog ON [' + @Catalog + '].[' + @Schema + '].[' + @Table + '] ([PointGeog]) WITH ( BOUNDING_BOX = ( '' + CONVERT(VARCHAR, @minlong) + '', '' + CONVERT(VARCHAR, @minlat) + '', '' + CONVERT(VARCHAR, @maxlong) + '', '' + CONVERT(VARCHAR, @maxlat) + ''), GRIDS =(LEVEL_1 = HIGH, LEVEL_2 = HIGH, LEVEL_3 = HIGH, LEVEL_4 = HIGH), CELLS_PER_OBJECT = 16)''' + @NewLineChar
280 + 'EXEC(@Sql)'
281
282 EXEC(@Sql)
283
284 PRINT CONVERT(VARCHAR, GETDATE()) + ': Spatial Index Created on PointGeog column'
285 END
286 END
287
288 PRINT CONVERT(VARCHAR, GETDATE()) + ': Creating Spatial index on [' + @GeomColumn + ']'
289
290 SET @Sql = 'DECLARE @minlat FLOAT, @minlong FLOAT, @maxlat FLOAT, @maxlong FLOAT, @Sql VARCHAR(MAX)' + @NewLineChar
291 + 'SELECT @minlat = MIN(A.MinLat), @minlong = MIN(A.MinLong), @maxlat = MAX(A.MaxLat), @maxlong = MAX(A.MaxLong) FROM [' + @Catalog + '].[' + @Schema + '].[' + @Table + '] A' + @NewLineChar
292 + 'SET @Sql = ''CREATE SPATIAL INDEX idx_' + @Table + '_' + @GeomColumn + ' ON [' + @Catalog + '].[' + @Schema + '].[' + @Table + '] ([' + @GeomColumn + ']) WITH ( BOUNDING_BOX = ( '' + CONVERT(VARCHAR, @minlong) + '', '' + CONVERT(VARCHAR, @minlat) + '', '' + CONVERT(VARCHAR, @maxlong) + '', '' + CONVERT(VARCHAR, @maxlat) + ''), GRIDS =(LEVEL_1 = HIGH, LEVEL_2 = HIGH, LEVEL_3 = HIGH, LEVEL_4 = HIGH), CELLS_PER_OBJECT = 16)''' + @NewLineChar
293 + 'EXEC(@Sql)'
294
295 EXEC(@Sql)
296
297 PRINT CONVERT(VARCHAR, GETDATE()) + ': Spatial Index Created on [' + @GeomColumn + '] column'
298 PRINT CONVERT(VARCHAR, GETDATE()) + ': Consuming #Tables record'
299
300 DELETE #Tables
301 WHERE TABLE_CATALOG = @Catalog
302 AND TABLE_SCHEMA = @Schema
303 AND TABLE_NAME = @Table
304
305 SELECT @Catalog = A.TABLE_CATALOG,
306 @Schema = A.TABLE_SCHEMA,
307 @Table = A.TABLE_NAME
308 FROM #Tables A
309END
310
311DROP TABLE #Tables
312
313PRINT CONVERT(VARCHAR, GETDATE()) + ': Script finished!'