gipasoft

Change Collation for all Column of all Table

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