none
how to correct wrong collation has been used RRS feed

  • Question

  • Dear all,

    I get a weird issue in one of our database. Actually we notice that wrong collation has been used for tables.
    We have set the correct collation at database level but seems we have still somw wronf collation at table levels.

    Actually we have some picture named field where we have character "é" but due to bad initial collation, record has not been encoded properly .

    Question :

    1- Is there a way to query all table columns with actually used collation ?

    2 - how to query table which have different column collation that database collation

    3 - What is the best way to correct table column collation ?

    Thanks for you sharing

    Friday, September 6, 2019 12:26 PM

Answers

  • Hi wakefun,

     

    If you would like to query all table columns with actually used collation, please refer to following  article. View Collation Information

     

    SELECT t.name TableName, c.name ColumnName, collation_name 
    FROM sys.columns c 
    inner join sys.tables t on c.object_id = t.object_id;


    Also , there is an article about Change collations of all columns of all tables in SQL Server, please refer to it. It will provide the script which runs through all user tables and collects the columns.

     

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by wakefun Monday, September 9, 2019 9:50 AM
    Monday, September 9, 2019 6:55 AM

All replies

  • The effective collation is defined on column level, not on table nor database; you would have to Change every column.

    You can query it with

    select t.name as tablename, c.name as columnname, c.collation_name
    from sys.tables as t
         inner join
         sys.columns as c
             on t.object_id = c.object_id
    where not c.collation_name is null
    order by t.name, c.name


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, September 6, 2019 1:04 PM
  • Hi Friend,

    Below Query will answer 1 &2 questions:

    select c.name+'.'+ b.name as tablename, 
    a.name as columnname,collation_name, b1.DBcollation as DBCollation,
    case 
    when collation_name<>DBCollation then 'Different Collation than DB Collation'
    else
    'Same as DB Collation'
    end Table_DB_Collation
    from sys.columns a
    join sys.objects   b
    on a.object_id=b.object_id
    join sys.schemas c
    on c.schema_id=b.schema_id
    cross join( SELECT DATABASEPROPERTYEX('Training','Collation') as DBcollation ) b1
    where collation_name is not null 
    and b.type not in ('S','IT''IF')
    order by b.name,a.name
     

    3. For 3rd question, We can generate ALTER Scripts using System tables to change the collation type


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    Friday, September 6, 2019 1:17 PM
  • Thanks for your reply,

    I am actually strugling with the point 3, to update all table collumn with correct collation.

    Do you have sampls of best effective alter query to do this ?

    regards

    Friday, September 6, 2019 1:44 PM
  • ALTER TABLE [YOUR_TABLE]
    ALTER COLUMN [YOUR_COLUMN] varchar(xxx) COLLATE COLLATION_YOU_WANT;

    A Fan of SSIS, SSRS and SSAS

    Friday, September 6, 2019 2:11 PM
  • Hi Wake,

    Please refer following SQL Script and try to test in some test tables

    SELECT 'ALTER TABLE '+tablename +' ALTER COLUMN  '+columnname+' ' 
    +DataType+'('+CAST(MaxLength as varchar(50))+')  COLLATE '+CAST(DBCollation as varchar)+' NULL'    
    FROM
    (
    select c.name+'.'+ b.name as tablename, 
    a.name as columnname,a.collation_name, b1.DBcollation as DBCollation,
    case 
    when a.collation_name<>DBCollation then 'Different Collation than DB Collation'
    else
    'Same as DB Collation'
    end Table_DB_Collation  ,
    t.Name 'Datatype',
        a.max_length 'MaxLength',
        a.precision ,
        a.scale ,
       a.is_nullable ,
       b.type
    from sys.columns a
    join sys.objects   b
    on a.object_id=b.object_id
    join sys.schemas c
    on c.schema_id=b.schema_id
    JOIN   sys.types t 
    ON a.user_type_id = t.user_type_id
    cross join( SELECT DATABASEPROPERTYEX('Training','Collation') as DBcollation ) b1
    where a.collation_name is not null 
    and b.type not in ('S','IT''IF')
    --and c.name<>'sys'
    )abc
    --order by b.name,a.name ) abc
    where Table_DB_Collation='Different Collation than DB Collation'


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    Friday, September 6, 2019 2:59 PM
  • Hi wakefun,

    Hope the below scripts will surely help you! 

    --> 1- Is there a way to query all table columns with actually used collation ?
    select o.name as table_name,c.name as column_name,c.collation_name    ,*
    FROM sys.tables o
    INNER JOIN sys.columns c ON o.OBJECT_ID = c.OBJECT_ID
    INNER JOIN sys.schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
    WHERE c.collation_name=CONVERT (varchar(256), SERVERPROPERTY('collation')) --default server collation
    and is_ms_shipped=0; --user created table columns alone (comment nthis filter to see all table columns based on requirement)
    
    
    --> 2 - how to query table which have different column collation that database collation
    
    --testing to make one dummy table dummy column as different collate
    /*
    ALTER TABLE STUDENTS
    ALTER COLUMN Student_Name VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS ; --> making 'case senstitive'
    */
    
    select o.name as table_name,c.name as column_name,c.collation_name    ,*
    FROM sys.tables o
    INNER JOIN sys.columns c ON o.OBJECT_ID = c.OBJECT_ID
    INNER JOIN sys.schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
    WHERE c.collation_name<>CONVERT (varchar(256), SERVERPROPERTY('collation')) --default server collation
    and is_ms_shipped=0; --user created table columns alone (comment nthis filter to see all table columns based on requirement)
    
    
    -->3 - What is the best way to correct table column collation ? (to get data_type, went to 'INFORMATION_SCHEMA.COLUMNS' table)
    DECLARE @collation_name VARCHAR(500);
    SET @collation_name=CONVERT (varchar(256), SERVERPROPERTY('collation'));
    --SELECT @collation_name;
    SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) +
           ' ALTER COLUMN ' + QUOTENAME(COLUMN_NAME) + QUOTENAME(DATA_TYPE) + ' COLLATE ' + @collation_name +
    	   CASE WHEN is_nullable='NO' THEN ' NOT NULL ' ELSE ' NULL ' END + ' ; '
    AS [Dynamic_Scripts]
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE collation_name<>CONVERT (varchar(256), SERVERPROPERTY('collation')); --default server collation

    Note: Suggested one of the many ways that is easy to understand and implement.
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Friday, September 6, 2019 7:33 PM
  • Dear Arulmuzi, does the point 3 querry will iterate through all table list return from point 2 ?

    regards

    Monday, September 9, 2019 6:26 AM
  • Hi wakefun,

     

    If you would like to query all table columns with actually used collation, please refer to following  article. View Collation Information

     

    SELECT t.name TableName, c.name ColumnName, collation_name 
    FROM sys.columns c 
    inner join sys.tables t on c.object_id = t.object_id;


    Also , there is an article about Change collations of all columns of all tables in SQL Server, please refer to it. It will provide the script which runs through all user tables and collects the columns.

     

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by wakefun Monday, September 9, 2019 9:50 AM
    Monday, September 9, 2019 6:55 AM
  • Yes, we can do the same using single table 'INFORMATION_SCHEMA.COLUMNS'.

    Please try the below scripts :

    --> 1- Is there a way to query all table columns with actually used collation ?
    DECLARE @collation_name VARCHAR(500);
    SET @collation_name=NULL; --give here your actual used collation name 
    --it takes default server collation if you haven't provide actual collation
    SET @collation_name=ISNULL(@collation_name,(CONVERT (varchar(256), SERVERPROPERTY('collation')))); 
    
    select table_name, column_name,collation_name,is_nullable,data_type,*
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE collation_name=@collation_name; 
    
    
    --> 2 - how to query table which have different column collation that database collation?
    select table_name, column_name,collation_name,is_nullable,data_type,*
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE collation_name<>CONVERT (varchar(256), SERVERPROPERTY('collation')); 
    
    
    -->3 - What is the best way to correct table column collation ? 
    --> My Suggestion is to generate scripts like these and cross check and execute.
    DECLARE @collationname VARCHAR(500);
    SET @collationname=NULL; --give here your actual used collation name 
    --it takes default server collation if you haven't provide actual collation
    SET @collationname=ISNULL(@collationname,(CONVERT (varchar(256), SERVERPROPERTY('collation')))); 
    
    SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) +
           ' ALTER COLUMN ' + QUOTENAME(COLUMN_NAME) + QUOTENAME(DATA_TYPE) + ' COLLATE ' + @collationname +
    	   CASE WHEN is_nullable='NO' THEN ' NOT NULL ' ELSE ' NULL ' END + ' ; '
    AS [Dynamic_Scripts]
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE collation_name<>@collationname;

    Note: Suggested one of the many ways that is easy to understand and implement.
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Monday, September 9, 2019 6:53 PM
  • Hi wakefun,

    The Attached script(3rd question) works well without changing your column nullability.

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Monday, September 9, 2019 6:55 PM