-
Notifications
You must be signed in to change notification settings - Fork 1
/
dropNCIs.sql
52 lines (43 loc) · 1.82 KB
/
dropNCIs.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
DECLARE @SchemaName VARCHAR(256)DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @TSQLDropIndex VARCHAR(MAX)
DECLARE CursorIndexes CURSOR FOR
SELECT schema_name(t.schema_id), t.name, i.name
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id= i.object_id
WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
and (is_primary_key=0 )
and i.type = 2
--and FILEGROUP_NAME(i.data_space_id) = 'PRIMARY'
--and is_unique_constraint=0)
OPEN CursorIndexes
FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName
WHILE @@fetch_status = 0
BEGIN
SET @TSQLDropIndex = 'DROP INDEX '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + '.' +QUOTENAME(@IndexName)
PRINT @TSQLDropIndex
FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName
END
CLOSE CursorIndexes
DEALLOCATE CursorIndexes
/*
DROP INDEX [dbo].[zooMonochromeBias].[i_zooMonochromeBias_objID]
DROP INDEX [dbo].[zooMirrorBias].[i_zooMirrorBias_objID]
DROP INDEX [dbo].[DataConstants].[i_DataConstants_value]
DROP INDEX [dbo].[RegionPatch].[i_RegionPatch_htmID_ra_dec_x_y_z]
DROP INDEX [dbo].[PlateX].[i_PlateX_htmID_ra_dec_cx_cy_cz]
DROP INDEX [dbo].[sdssTileAll].[i_sdssTileAll_tileRun_tile]
DROP INDEX [dbo].[sdssTileAll].[i_sdssTileAll_htmID_racen_deccen]
DROP INDEX [dbo].[zooNoSpec].[i_zooNoSpec_objID]
*/
/*
DROP INDEX [dbo].[apogeeObject].[i_apogeeObject_apogee_id_j_h_k_j]
DROP INDEX [dbo].[apogeeStar].[i_apogeeStar_apogee_id]
DROP INDEX [dbo].[apogeeStar].[i_apogeeStar_htmID]
DROP INDEX [dbo].[apogeeVisit].[i_apogeeVisit_apogee_id]
DROP INDEX [dbo].[apogeeVisit].[i_apogeeVisit_plate_mjd_fiberid]
DROP INDEX [dbo].[aspcapStar].[i_aspcapStar_apstar_id]
DROP INDEX [dbo].[PlateX].[i_PlateX_htmID_ra_dec_cx_cy_cz]
DROP INDEX [dbo].[zooConfidence].[i_zooConfidence_objID]
DROP INDEX [dbo].[zooSpec].[i_zooSpec_objID]
*/