StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As I did not find a proper way I wrote a script to do it and I'm sharing it here for those who need it. The script runs through all user tables and collects the columns. If the column type is any char type then it tries to convert it to the given collation.</p> <p>Columns has to be index and constraint free for this to work.</p> <p><strong>If someone still has a better solution to this please post it!</strong></p> <pre><code>DECLARE @collate nvarchar(100); DECLARE @table nvarchar(255); DECLARE @column_name nvarchar(255); DECLARE @column_id int; DECLARE @data_type nvarchar(255); DECLARE @max_length int; DECLARE @row_id int; DECLARE @sql nvarchar(max); DECLARE @sql_column nvarchar(max); SET @collate = 'Latin1_General_CI_AS'; DECLARE local_table_cursor CURSOR FOR SELECT [name] FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 OPEN local_table_cursor FETCH NEXT FROM local_table_cursor INTO @table WHILE @@FETCH_STATUS = 0 BEGIN DECLARE local_change_cursor CURSOR FOR SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id , c.name column_name , t.Name data_type , c.max_length , c.column_id FROM sys.columns c JOIN sys.types t ON c.system_type_id = t.system_type_id LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id WHERE c.object_id = OBJECT_ID(@table) ORDER BY c.column_id OPEN local_change_cursor FETCH NEXT FROM local_change_cursor INTO @row_id, @column_name, @data_type, @max_length, @column_id WHILE @@FETCH_STATUS = 0 BEGIN IF (@max_length = -1) OR (@max_length > 4000) SET @max_length = 4000; IF (@data_type LIKE '%char%') BEGIN TRY SET @sql = 'ALTER TABLE ' + @table + ' ALTER COLUMN ' + @column_name + ' ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) + ') COLLATE ' + @collate PRINT @sql EXEC sp_executesql @sql END TRY BEGIN CATCH PRINT 'ERROR: Some index or constraint rely on the column' + @column_name + '. No conversion possible.' PRINT @sql END CATCH FETCH NEXT FROM local_change_cursor INTO @row_id, @column_name, @data_type, @max_length, @column_id END CLOSE local_change_cursor DEALLOCATE local_change_cursor FETCH NEXT FROM local_table_cursor INTO @table END CLOSE local_table_cursor DEALLOCATE local_table_cursor GO </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload