update all tables collate database default

1DECLARE @collate nvarchar(100);
2DECLARE @table nvarchar(255);
3DECLARE @column_name nvarchar(255);
4DECLARE @column_id int;
5DECLARE @data_type nvarchar(255);
6DECLARE @max_length int;
7DECLARE @row_id int;
8DECLARE @sql nvarchar(max);
9DECLARE @sql_column nvarchar(max);
10
11SET @collate = 'Latin1_General_CI_AS';
12
13DECLARE local_table_cursor CURSOR FOR
14
15SELECT [name]
16FROM sysobjects
17WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1
18
19OPEN local_table_cursor
20FETCH NEXT FROM local_table_cursor
21INTO @table
22
23WHILE @@FETCH_STATUS = 0
24BEGIN
25
26    DECLARE local_change_cursor CURSOR FOR
27
28    SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
29        , c.name column_name
30        , t.Name data_type
31        , c.max_length
32        , c.column_id
33    FROM sys.columns c
34    JOIN sys.types t ON c.system_type_id = t.system_type_id
35    LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
36    LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
37    WHERE c.object_id = OBJECT_ID(@table)
38    ORDER BY c.column_id
39
40    OPEN local_change_cursor
41    FETCH NEXT FROM local_change_cursor
42    INTO @row_id, @column_name, @data_type, @max_length, @column_id
43
44    WHILE @@FETCH_STATUS = 0
45    BEGIN
46
47        IF (@max_length = -1) OR (@max_length > 4000) SET @max_length = 4000;
48
49        IF (@data_type LIKE '%char%')
50        BEGIN TRY
51            SET @sql = 'ALTER TABLE ' + @table + ' ALTER COLUMN ' + @column_name + ' ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) + ') COLLATE ' + @collate
52            PRINT @sql
53            EXEC sp_executesql @sql
54        END TRY
55        BEGIN CATCH
56          PRINT 'ERROR: Some index or constraint rely on the column' + @column_name + '. No conversion possible.'
57          PRINT @sql
58        END CATCH
59
60        FETCH NEXT FROM local_change_cursor
61        INTO @row_id, @column_name, @data_type, @max_length, @column_id
62
63    END
64
65    CLOSE local_change_cursor
66    DEALLOCATE local_change_cursor
67
68    FETCH NEXT FROM local_table_cursor
69    INTO @table
70
71END
72
73CLOSE local_table_cursor
74DEALLOCATE local_table_cursor
75
76GO
77