Archive

Posts Tagged ‘Collation’

SQL – Modify collation

October 23, 2011 Leave a comment

In database environments, we will get situations of modifying the Collation of servers, databases and tables.
Modifying the collation of server and databases will go with graphical interface or a single script however changing the collation of individual tables or columns will be a big challenge as it requires writing manual script apart from the limitations.
Here I wrote one dynamic script to modify the tables’ collation which includes a filter condition if we want to apply only for a particular table

USE [AdventureWorks]

DECLARE @statement VARCHAR(1000)
DECLARE @condition VARCHAR(1000)
DECLARE @rowCount INT
DECLARE @NewCollation VARCHAR(100) = 'SQL_Latin1_General_CP1_CI_AS' 

DECLARE @primarytable TABLE
    (
      TABLE_SCHEMA VARCHAR(250),
      TABLE_NAME VARCHAR(250),
      COLUMN_NAME VARCHAR(250),
      DATA_TYPE VARCHAR(250),
      CHARACTER_MAXIMUM_LENGTH VARCHAR(250),
      IS_NULLABLE VARCHAR(250),
      COLLATION_NAME VARCHAR(250)
    )

DECLARE @secondarytable TABLE
    (
      TABLE_SCHEMA VARCHAR(250),
      TABLE_NAME VARCHAR(250),
      COLUMN_NAME VARCHAR(250),
      DATA_TYPE VARCHAR(250),
      CHARACTER_MAXIMUM_LENGTH VARCHAR(250),
      IS_NULLABLE VARCHAR(250),
      COLLATION_NAME VARCHAR(250)
    )

BEGIN TRY	
	

    INSERT  INTO @primarytable
            SELECT  C.TABLE_SCHEMA,
                    C.TABLE_NAME,
                    C.COLUMN_NAME,
                    C.DATA_TYPE,
                    C.CHARACTER_MAXIMUM_LENGTH,
                    C.IS_NULLABLE,
                    C.COLLATION_NAME
            FROM    INFORMATION_SCHEMA.COLUMNS C
                    JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME
            WHERE   C.COLLATION_NAME IS NOT NULL
                    AND T.TABLE_TYPE = 'BASE TABLE'
               -- AND T.TABLE_NAME = 'Vendor'  --- Enter the table name if need to apply for a particular table
            ORDER BY C.TABLE_NAME DESC

    SELECT  @rowCount = COUNT(*)
    FROM    @primarytable ;
    WHILE ( @rowCount != 0 )
        BEGIN

            INSERT  INTO @secondarytable
                    SELECT TOP 1
                            *
                    FROM    @primarytable ;

            SELECT  @condition = CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL'
                                      ELSE 'NULL'
                                 END
            FROM    @primarytable

            SELECT  @statement = 'ALTER TABLE [' + TABLE_SCHEMA + '].['
                    + TABLE_NAME + ']' + CHAR(13) + 'ALTER COLUMN ['
                    + COLUMN_NAME + '] ' + DATA_TYPE + '('
                    + CHARACTER_MAXIMUM_LENGTH + ')' + CHAR(13) + 'COLLATE '
                    + @NewCollation + ' ' + @condition
            FROM    @secondarytable
	                      
            PRINT @STATEMENT
            EXEC ( @STATEMENT
                )

            DELETE TOP ( 1 )
            FROM    @primarytable ;
            SELECT  @rowCount = COUNT(*)
            FROM    @primarytable ;
        END
END TRY	

BEGIN CATCH 
    SELECT  ERROR_NUMBER() AS ErrorNumber
    SELECT  ERROR_MESSAGE() AS ErrorMessage
END CATCH	

 
Script to check the collation of any table

-- Testing script for any table
-- Test the existing collation of any table
SELECT  name,
        collation_name
FROM    sys.columns
WHERE   OBJECT_ID IN ( SELECT   OBJECT_ID
                       FROM     sys.objects
                       WHERE    type = 'U'
                                AND name = 'DatabaseLog' ) -- Enter table name 

 
Limitations:
 
Modifying collation of any column has limitations, we couldn’t able to change the collation if the column is
1. A computed column
2. An index or part of the index
3. Contributed to the STATISTICS of the table
4. Defined any CHECK constraint
5. Having FOREIGN KEY/ALTERNATE KEY constraint
In the situation of limitations, we need to drop and create any of the limitations by considering the suitable to the particular database or environment
 
Points to consider
 
1. We could write the above script in many ways i.e. using CURSOR and CTE etc.
2. I considered writing the script in a simple way to understand for any non SQL Server developer.
3. I haven’t presented the results here as it has own TRY and CATCH block so we could understand any of the limitations in the process of applying the script

Categories: SQL Server Tags: