gipasoft

Change All Columns Collation

Apr 12th, 2018
296
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.17 KB | None | 0 0
  1. USE Fad05roadmapanestesia15
  2.  
  3. DECLARE @COLLATE nvarchar(100);
  4. DECLARE @TABLE nvarchar(255);
  5. DECLARE @column_name nvarchar(255);
  6. DECLARE @column_id INT;
  7. DECLARE @data_type nvarchar(255);
  8. DECLARE @max_length INT;
  9. DECLARE @row_id INT;
  10. DECLARE @SQL nvarchar(MAX);
  11. DECLARE @sql_column nvarchar(MAX);
  12.  
  13. SET @COLLATE = 'Latin1_General_CI_AS';
  14.  
  15. DECLARE local_table_cursor CURSOR FOR
  16.  
  17. SELECT [name]
  18. FROM sysobjects
  19. WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1
  20.  
  21. OPEN local_table_cursor
  22. FETCH NEXT FROM local_table_cursor
  23. INTO @TABLE
  24.  
  25. WHILE @@FETCH_STATUS = 0
  26. BEGIN
  27.  
  28.     DECLARE local_change_cursor CURSOR FOR
  29.  
  30.     SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
  31.         , c.name column_name
  32.         , t.Name data_type
  33.         , c.max_length
  34.         , c.column_id
  35.     FROM sys.COLUMNS c
  36.     JOIN sys.types t ON c.system_type_id = t.system_type_id
  37.     LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
  38.     LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
  39.     WHERE c.object_id = OBJECT_ID(@TABLE)
  40.     ORDER BY c.column_id
  41.  
  42.     OPEN local_change_cursor
  43.     FETCH NEXT FROM local_change_cursor
  44.     INTO @row_id, @column_name, @data_type, @max_length, @column_id
  45.  
  46.     WHILE @@FETCH_STATUS = 0
  47.     BEGIN
  48.  
  49.         IF (@max_length = -1) OR (@max_length > 4000) SET @max_length = 4000;
  50.  
  51.         IF (@data_type LIKE '%char%')
  52.         BEGIN TRY
  53.             SET @SQL = 'ALTER TABLE ' + @TABLE + ' ALTER COLUMN ' + @column_name + ' ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) + ') COLLATE ' + @COLLATE
  54.             PRINT @SQL
  55.             EXEC sp_executesql @SQL
  56.         END TRY
  57.         BEGIN CATCH
  58.           PRINT 'ERROR: Some index or constraint rely on the column' + @column_name + '. No conversion possible.'
  59.           PRINT @SQL
  60.         END CATCH
  61.  
  62.         FETCH NEXT FROM local_change_cursor
  63.         INTO @row_id, @column_name, @data_type, @max_length, @column_id
  64.  
  65.     END
  66.  
  67.     CLOSE local_change_cursor
  68.     DEALLOCATE local_change_cursor
  69.  
  70.     FETCH NEXT FROM local_table_cursor
  71.     INTO @TABLE
  72.  
  73. END
  74.  
  75. CLOSE local_table_cursor
  76. DEALLOCATE local_table_cursor
  77.  
  78. GO
Advertisement
Add Comment
Please, Sign In to add comment