Change collations of all columns of all tables in SQL Server

Question

I imported a database with some data to compare with another database  The target database has collation Latin1 General CI AS and the source database has SQL Latin1 General CP1 CI AS  I did change the collation of the source database in general to Latin1 General CI AS using the SQL Server Management Studio  But the tables and columns inside remains with the old collation  I know that I can change a column using  ALTER TABLE  table   ALTER COLUMN  column  VARCHAR 100  COLLATE Latin1 General CI AS  But I have to do this for all tables and all columns inside  Before I know start to write a stored procedure that reads all tables and inside all column of type varchar and change them in a table and column cursor loop    Does anyone know an easier way or is the only way to do this with a script running through all tables in a procedure

User · Answer

Using the cursor based variations above as a starting point, the script below will just output a set of UPDATE statements to set to DATABASE_DEFAULT, it won't actually do the UPDATES.

It supports schema, the full set of char and text types and retains the existing NULL / NOT NULL.

I plan to use the output to find to statements that fail in a lower environment and then manually adapt the resulting script to drop and recreate the constraints as needed.

DECLARE @collate nvarchar(100);
DECLARE @schema nvarchar(255);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @max_length_str nvarchar(100);
DECLARE @is_nullable bit;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);

SET @collate = 'DATABASE_DEFAULT';

DECLARE local_table_cursor CURSOR FOR

SELECT (s.[name])schemaName, (o.[name])[tableName]
FROM sysobjects sy 
INNER JOIN sys.objects  o on o.name = sy.name
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE OBJECTPROPERTY(sy.id, N'IsUserTable') = 1
ORDER BY s.[name], o.[name]

OPEN local_table_cursor FETCH NEXT FROM local_table_cursor INTO @schema,@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
        , col.CHARACTER_MAXIMUM_LENGTH
        , c.column_id
        , c.is_nullable
    FROM sys.columns c
    JOIN sys.types t ON c.system_type_id = t.system_type_id
    JOIN INFORMATION_SCHEMA.COLUMNS col on col.COLUMN_NAME = c.name and c.object_id = OBJECT_ID(col.TABLE_NAME)
    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(@schema+'.'+@table) AND (t.Name LIKE '%char%' OR t.Name LIKE '%text%') 
    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, @is_nullable

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @max_length_str = @max_length
        IF (@max_length = -1) SET @max_length_str = 'max'
        IF (@max_length > 4000) SET @max_length_str = '4000'

        SET @sql =
        CASE 
            WHEN @data_type like '%text%' 
            THEN 'ALTER TABLE [' + @schema+ '].['+ @table + '] ALTER COLUMN [' + @column_name + '] ' + @data_type + ' COLLATE ' + @collate + ' ' + CASE WHEN @is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
            ELSE 'ALTER TABLE [' + @schema+ '].['+ @table + '] ALTER COLUMN [' + @column_name + '] ' + @data_type + '(' + @max_length_str + ') COLLATE ' + @collate + ' ' + CASE WHEN @is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
        END
        PRINT @sql

        FETCH NEXT FROM local_change_cursor
        INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable

    END

    CLOSE local_change_cursor
    DEALLOCATE local_change_cursor

    FETCH NEXT FROM local_table_cursor
    INTO @schema, @table
END

CLOSE local_table_cursor
DEALLOCATE local_table_cursor
GO

User · Answer

I always prefer pure SQL so     SELECT  ALTER TABLE      l schema n                   l table name      ALTER COLUMN              l column name          l data type                 Cast l new max length AS NVARCHAR 100                COLLATE     l dest collation name                l schema n          l table name          l column name          l data type          l max length          l collation name  FROM    SELECT Row number                     OVER                       ORDER BY c column id  AS row id                  Schema name o schema id   schema n                  ta NAME                   table name                  c NAME                    column name                  t NAME                    data type                  c max length                  CASE                   WHEN c max length   -1                         OR   c max length  gt  4000   THEN 4000                   ELSE c max length                 END                       new max length                  c column id                  c collation name                   French CI AS             dest collation name          FROM   sys columns c                 INNER JOIN sys tables ta                         ON c object id   ta object id                 INNER JOIN sys objects o                         ON c object id   o object id                 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  1   1                 AND c collation name    SQL Latin1 General CP1 CI AS          -- French CI AS -- ALTER DONE YET OLD VALUE   SQL Latin1 General CP1 CI AS            l  ORDER  BY l column id

User · Answer

To do this I have an easy solution that works for me    Create a new database with the new collation  Export the data of the original data base in script mode  Import the content to the new database using the script  rename the USE sentence to the new database        However  you need to exercise caution if your database has triggers  procedures or similar - more that just data and tables

User · Answer

I made a little change on the script   DECLARE  collate nvarchar 100   DECLARE  table sysname  DECLARE  schema sysname  DECLARE  objectId int  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   DECLARE  is Nullable bit  DECLARE  null nvarchar 25    SET  collate    Latin1 General CI AS    DECLARE local table cursor CURSOR FOR  SELECT tbl TABLE SCHEMA  name  obj id FROM sysobjects as obj inner join INFORMATION SCHEMA TABLES as tbl on obj name   tbl TABLE NAME WHERE OBJECTPROPERTY obj id  N IsUserTable     1  OPEN local table cursor FETCH NEXT FROM local table cursor INTO  schema   table   objectId   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           c is nullable     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    objectId     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   is nullable      WHILE   FETCH STATUS   0     BEGIN         IF   max length   -1  SET  max length   4000          set  null   NOT NULL          if   is nullable   1  Set  null   NULL          if   Data type  nvarchar   set  max length cast  max length 2 as bigint          IF   data type LIKE   char            BEGIN TRY             SET  sql    ALTER TABLE      schema          table     ALTER COLUMN      rtrim  column name            data type         CAST  max length AS nvarchar 100         COLLATE      collate    null             PRINT  sql             EXEC sp executesql  sql         END TRY         BEGIN CATCH           PRINT  ERROR  Some index or contraint 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   is Nullable      END      CLOSE local change cursor     DEALLOCATE local change cursor      FETCH NEXT FROM local table cursor     INTO  schema  table  objectId  END  CLOSE local table cursor DEALLOCATE local table cursor  GO

User · Answer

Fixed length problem nvarchar  include max   included text and added NULL NOT NULL   USE  put your database name here    begin tran  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  max length str nvarchar 100   DECLARE  is nullable bit  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 ORDER BY  name   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           col CHARACTER MAXIMUM LENGTH           c column id           c is nullable     FROM sys columns c     JOIN sys types t ON c system type id   t system type id     JOIN INFORMATION SCHEMA COLUMNS col on col COLUMN NAME   c name and c object id   OBJECT ID col TABLE NAME      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  AND  t Name LIKE   char   OR t Name LIKE   text         AND c collation name  lt  gt   collate     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   is nullable      WHILE   FETCH STATUS   0     BEGIN          set  max length str    max length         IF   max length   -1  SET  max length str    max          IF   max length  gt  4000  SET  max length str    4000           BEGIN TRY             SET  sql               CASE                  WHEN  data type like   text                    THEN  ALTER TABLE      table     ALTER COLUMN       column name           data type     COLLATE      collate         CASE WHEN  is nullable   0 THEN  NOT NULL  ELSE  NULL  END                 ELSE  ALTER TABLE      table     ALTER COLUMN       column name           data type          max length str      COLLATE      collate         CASE WHEN  is nullable   0 THEN  NOT NULL  ELSE  NULL  END             END             --PRINT  sql             EXEC sp executesql  sql         END TRY         BEGIN CATCH           PRINT  ERROR       table       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   is nullable      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  commit tran  GO   Notice   in case when you just need to change some specific collation use condition like this    WHERE c object id   OBJECT ID  table  AND  t Name LIKE   char   OR t Name LIKE   text         AND c collation name    collation to change    e g  NOT the   AND c collation name  lt  gt   collate  In my case  I had correct   specified collation of some columns and didn t want to change them

User · Answer

So here I am  once again  not satisfied with the answer  I was tasked to upgrade JIRA 6 4 x to JIRA Software 7 x and I went to that particular problem with the database and column collation    In SQL Server  if you do not drop constrains such as primary key or foreign key or even indexes  the script provided above as an answer doesn t work at all  It will however change those without those properties  This is really problematic  because I don t want to manually drop all constrains and create them back  That operation could probably ends up with errors  On the other side  creating a script automating the change could take ages to make    So I found a way to make the migration simply by using SQL Management Studio  Here s the procedure     Rename the database by something else  By example  mine s was  Jira   so I renamed it  JiraTemp    Create a new database named  Jira  and make sure to set the right collation  Simply select the page  Options  and change the collation  Once created  go back to  JiraTemp   right click it   Tasks -  Generate Scripts         Select  Script entire database and all database objects   Select  Save to new query window   then select  Advanced  Change the value of  Script for Server Version  for the desired value Enable  Script Object-Level Permissions    Script Owner  and  Script Full-Text Indexes  Leave everything else as is or personalize it if you wish   Once generated  delete the  CREATE DATABASE  section  Replace  JiraTemp  by  Jira   Run the script  The entire database structure and permissions of the database is now replicated to  Jira    Before we copy the data  we need to disable all constrains  Execute the following command to do so in the database  Jira   EXEC sp msforeachtable  ALTER TABLE   NOCHECK CONSTRAINT all  Now the data needs to be transferred  To do so  simply right click  JiraTemp   then select  Tasks -  Export Data       Select as data source and destination the OLE DB Provider for SQL Server   Source database is  JiraTemp  Destination database is  Jira  The server name is technically the same for source and destination  except if you ve created the database on another server   Select  Copy data from one or another tables or views  Select all tables except views  Then  when still highlighted  click on  Edit Mappings   Check  Enable identity insert  Click OK  Next  then Finish  Data transfer can take a while  Once finished  execute the following command to re enable all constrains  exec sp msforeachtable  command1  print        command2  ALTER TABLE   WITH CHECK CHECK CONSTRAINT all    Once completed  I ve restarted JIRA and my database collation was in order  Hope it helps a lot of people

User · Answer

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   Columns has to be index and constraint free for this to work   If someone still has a better solution to this please post it   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  gt  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

User · Answer

Fixed length problem nvarchar and added NULL NOT NULL  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   DECLARE  is Nullable bit  DECLARE  null nvarchar 25    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           c is nullable     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   is nullable      WHILE   FETCH STATUS   0     BEGIN          IF   max length   -1  SET  max length   4000          set  null   NOT NULL          if   is nullable   1  Set  null   NULL          if   Data type  nvarchar   set  max length cast  max length 2 as bigint          IF   data type LIKE   char            BEGIN TRY             SET  sql    ALTER TABLE      table     ALTER COLUMN      rtrim  column name            data type         CAST  max length AS nvarchar 100         COLLATE      collate    null             PRINT  sql             EXEC sp executesql  sql         END TRY         BEGIN CATCH           PRINT  ERROR  Some index or contraint 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   is Nullable      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

User · Answer

Sorry late to the party  but here is mine - cater for table with a schema and funny column and table names  Yes I had some of them   SELECT       ALTER TABLE       TABLE SCHEMA           TABLE NAME            ALTER COLUMN      COLUMN NAME          DATA TYPE              CAST CHARACTER MAXIMUM LENGTH AS nvarchar 100             COLLATE      Latin1 General CI AS         CASE WHEN IS NULLABLE    YES  THEN   NULL  ELSE   NOT NULL  END  FROM      INFORMATION SCHEMA COLUMNS  WHERE      DATA TYPE like   char

User · Answer

Following script will work with table schema along with latest Types like  MAX   IMAGE  and etc   change your collation type according to your need on this line  SET  collate    DATABASE DEFAULT      SQL SCRIPT HERE   BEGIN DECLARE  collate nvarchar 100   declare  schema nvarchar 255   DECLARE  table nvarchar 255   DECLARE  column name nvarchar 255   DECLARE  column id int  DECLARE  data type nvarchar 255   DECLARE  max length varchar 100   DECLARE  row id int  DECLARE  sql nvarchar max   DECLARE  sql column nvarchar max    SET  collate    DATABASE DEFAULT    DECLARE tbl cursor CURSOR FOR SELECT  s  name  schemaName   o  name   tableName  FROM sysobjects sy  INNER JOIN sys objects  o on o name   sy name INNER JOIN sys schemas s ON o schema id   s schema id WHERE OBJECTPROPERTY sy id  N IsUserTable     1  OPEN tbl cursor FETCH NEXT FROM tbl cursor INTO  schema  table  WHILE   FETCH STATUS   0 BEGIN     DECLARE tbl cursor changed 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 like OBJECT ID  schema      table      ORDER BY c column id       OPEN tbl cursor changed       FETCH NEXT FROM tbl cursor changed     INTO  row id   column name   data type   max length   column id        WHILE   FETCH STATUS   0     BEGIN     IF   max length   -1  SET  max length    MAX           IF   data type LIKE   char            BEGIN TRY             SET  sql    ALTER TABLE     schema       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             PRINT  sql         END CATCH          FETCH NEXT FROM tbl cursor changed         INTO  row id   column name   data type   max length   column id      END      CLOSE tbl cursor changed     DEALLOCATE tbl cursor changed      FETCH NEXT FROM tbl cursor     INTO  schema   table  END  CLOSE tbl cursor DEALLOCATE tbl cursor  PRINT  Collation For All Tables Done   END

[sql-server] Change collations of all columns of all tables in SQL Server

Examples related to sql-server

Examples related to collate