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
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
 

--
-- Created by antonisV - 25/05/2008
--
DECLARE @TABLE_NAME AS VARCHAR(100)
DECLARE @VIEW_NAME AS VARCHAR(100)
DECLARE @COL_NAME AS VARCHAR(100)
DECLARE @IDX_NAME AS VARCHAR(100)
DECLARE @IS_PK AS NUMERIC(1)
DECLARE @IS_UNQ AS NUMERIC(1)
DECLARE @IDX_DESCR AS VARCHAR(20)
DECLARE @IDX_UNQ_CONS AS NUMERIC(1)
DECLARE @IDX_COL_SORT AS VARCHAR(10)

DECLARE @IDX_CREATE_STMT AS VARCHAR(2000)
DECLARE @IDX_DROP_STMT AS VARCHAR(2000)
DECLARE @ALL_TBL_IDX_STMT AS VARCHAR(8000)
DECLARE @ALTER_COLLATE_STMT AS VARCHAR(2000)

DECLARE @fromCollation sysname,
        @toCollation sysname

SET @fromCollation = 'SQL_Latin1_General_CP1253_CI_AI'
SET @toCollation = 'SQL_Latin1_General_CP1253_CS_AS'


/* Cursor for all user tables */
DECLARE SEL_USER_TABLES CURSOR FOR
  SELECT  TABLE_NAME
  FROM  INFORMATION_SCHEMA.TABLES
  WHERE  TABLE_TYPE <> 'VIEW'
  ORDER BY TABLE_NAME


/* Open Table cursor */
OPEN SEL_USER_TABLES

/* Get table name */
FETCH NEXT FROM SEL_USER_TABLES INTO @TABLE_NAME

/* Loop around tables */
WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @ALL_TBL_IDX_STMT = ''  /* Initialise create stmt for all indexes of current table */

    /* Cursor for indexes of specific table ordered by descending index_id */
    DECLARE SEL_IDX_INFO CURSOR FOR
      SELECT idx.name AS IndexName,
             idx.is_primary_key As isPK,
             idx.is_unique As isUNQ,
             idx.type_desc As descr,
             idx.is_unique_constraint
      FROM   sys.indexes AS idx
      INNER JOIN sys.tables AS tbl ON idx.OBJECT_ID = tbl.OBJECT_ID
      WHERE tbl.type = 'U'
      AND   OBJECT_NAME(tbl.OBJECT_ID) = @TABLE_NAME
      AND   idx.index_id <> 0
      ORDER BY  idx.index_id DESC

    /* Open Index cursor for current table */
    OPEN SEL_IDX_INFO

    /* Get index info */
    FETCH NEXT FROM SEL_IDX_INFO INTO @IDX_NAME, @IS_PK, @IS_UNQ, @IDX_DESCR, @IDX_UNQ_CONS
    
    /* Loop around indexes */
    WHILE @@FETCH_STATUS = 0
      BEGIN
        /* Start forming create stmt */
        IF @IS_PK = 1
          BEGIN  /* PRIMARY KEY */
            SET @IDX_CREATE_STMT = 'ALTER TABLE ['+@TABLE_NAME+'] ADD CONSTRAINT ['+@IDX_NAME+'] PRIMARY KEY CLUSTERED ( '
            SET @IDX_DROP_STMT = 'ALTER TABLE ['+@TABLE_NAME+'] DROP CONSTRAINT ['+@IDX_NAME+']'
          END
        ELSE
          BEGIN  /* NON-PRIMARY-KEY IDX */
            IF @IDX_UNQ_CONS = 1
              BEGIN  /* UNIQUE CONSTRAINT */
                SET @IDX_CREATE_STMT = 'ALTER TABLE ['+@TABLE_NAME+'] ADD CONSTRAINT ['+@IDX_NAME+'] UNIQUE NONCLUSTERED ( '
                SET @IDX_DROP_STMT = 'ALTER TABLE ['+@TABLE_NAME+'] DROP CONSTRAINT ['+@IDX_NAME+']'
              END
            ELSE
              BEGIN  /* SIMPLE INDEX */
                SET @IDX_DROP_STMT = 'DROP INDEX ['+@IDX_NAME+'] ON ['+@TABLE_NAME+']'
                SET @IDX_CREATE_STMT = 'CREATE '
                IF @IS_UNQ = 1
                  BEGIN  /* Unique IDX */
                    SET @IDX_CREATE_STMT = @IDX_CREATE_STMT+'UNIQUE '
                END
                IF @IDX_DESCR = 'CLUSTERED'
                  BEGIN  /* Clustered IDX */
                    SET @IDX_CREATE_STMT = @IDX_CREATE_STMT+'CLUSTERED '
                  END
                ELSE
                  BEGIN  /* Clustered IDX */
                    SET @IDX_CREATE_STMT = @IDX_CREATE_STMT+'NONCLUSTERED '
                END
                SET @IDX_CREATE_STMT = @IDX_CREATE_STMT+'INDEX ['+@IDX_NAME+'] ON ['+@TABLE_NAME+'] ( '
            END
        END

        /* Cursor for columns of a specific index ordered by ascending index_column_id */
        DECLARE SEL_IDX_COLS CURSOR FOR
          SELECT COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName,
                 CASE ic.is_descending_key
                  WHEN 1 THEN 'DESC'
                  ELSE 'ASC'
                 END AS ColumnSort
          FROM sys.indexes AS idx
          INNER JOIN sys.index_columns AS ic ON 
                (idx.OBJECT_ID = ic.OBJECT_ID AND idx.index_id = ic.index_id)
          INNER JOIN sys.tables AS tbl ON idx.OBJECT_ID = tbl.OBJECT_ID
          WHERE tbl.type = 'U'
          AND   OBJECT_NAME(ic.OBJECT_ID) = @TABLE_NAME
          AND   idx.name = @IDX_NAME
          ORDER BY  ic.index_column_id

        /* Open cloumns cursor for current index */
        OPEN SEL_IDX_COLS
        /* Get column name and sorting */
        FETCH NEXT FROM SEL_IDX_COLS INTO @COL_NAME, @IDX_COL_SORT
        /* Loop around columns */
        WHILE @@FETCH_STATUS = 0
          BEGIN
            /* Append to IDX create stmt */
            SET @IDX_CREATE_STMT = @IDX_CREATE_STMT+'['+@COL_NAME+'] '+@IDX_COL_SORT+', '
            /* Get next column name and sorting */
            FETCH NEXT FROM SEL_IDX_COLS INTO @COL_NAME, @IDX_COL_SORT
        END
        /* Complete IDX create stmt */
        SET @IDX_CREATE_STMT = RTRIM(@IDX_CREATE_STMT)
        SET @IDX_CREATE_STMT = LEFT( @IDX_CREATE_STMT, LEN(@IDX_CREATE_STMT)-1 )+' )'
        /* Close cloumns cursor for current index */
        CLOSE SEL_IDX_COLS
        DEALLOCATE SEL_IDX_COLS

        /* Append on the left of all indexes creation stmt, the current one */
        SET @ALL_TBL_IDX_STMT = @IDX_CREATE_STMT+' '+@ALL_TBL_IDX_STMT

        /* Drop current Index */
        EXEC (@IDX_DROP_STMT)

        /* Get next index info */
        FETCH NEXT FROM SEL_IDX_INFO INTO @IDX_NAME, @IS_PK, @IS_UNQ, @IDX_DESCR, @IDX_UNQ_CONS
    END
    /* Close Index cursor for current table */
    CLOSE SEL_IDX_INFO
    DEALLOCATE SEL_IDX_INFO

    /* Cursor for altering collation on a specific table's txt columns */
    DECLARE SEL_CHANGE_COLLATE CURSOR FOR
      SELECT  'ALTER TABLE ['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] '+DATA_TYPE+
          CASE
            WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN '(max)'
            WHEN DATA_TYPE IN ('text','ntext') THEN ''
            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
            ELSE ISNULL(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
          END
          +' COLLATE ' + @toCollation+' '+
          CASE IS_NULLABLE 
            WHEN 'YES' THEN 'NULL'
            WHEN 'No' THEN 'NOT NULL'
          END
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
    --  AND COLLATION_NAME LIKE @fromCollation 
      AND TABLE_NAME = @TABLE_NAME

    /* Alter Collation where needed on current table */
    OPEN SEL_CHANGE_COLLATE
    FETCH NEXT FROM SEL_CHANGE_COLLATE INTO @ALTER_COLLATE_STMT
    /* Loop around columns */
    WHILE @@FETCH_STATUS = 0
      BEGIN
        /* Execute alter command */
        EXEC(@ALTER_COLLATE_STMT)
        /* Get next alter stmt */
        FETCH NEXT FROM SEL_CHANGE_COLLATE INTO @ALTER_COLLATE_STMT
    END  
    CLOSE SEL_CHANGE_COLLATE
    DEALLOCATE SEL_CHANGE_COLLATE

    /* Recreate key and indexes on table */
    EXEC(@ALL_TBL_IDX_STMT)

    /* Get next table name */
    FETCH NEXT FROM SEL_USER_TABLES INTO @TABLE_NAME
END
/* Close table cursor */
CLOSE SEL_USER_TABLES
DEALLOCATE SEL_USER_TABLES


/* Cursor for all user views */
DECLARE SEL_USER_VIEWS CURSOR FOR
  SELECT  TABLE_NAME
  FROM  INFORMATION_SCHEMA.VIEWS
  ORDER BY TABLE_NAME

/* Open view cursor */
OPEN SEL_USER_VIEWS
/* Get view name */
FETCH NEXT FROM SEL_USER_VIEWS INTO @VIEW_NAME

/* Loop around views */
WHILE @@FETCH_STATUS = 0
  BEGIN
    /* Refresh view */
    exec sp_refreshview @VIEW_NAME
    /* Get next view name */
    FETCH NEXT FROM SEL_USER_VIEWS INTO @VIEW_NAME
END
/* Close view cursor */
CLOSE SEL_USER_VIEWS
DEALLOCATE SEL_USER_VIEWS