Script to change collation

I have (inherited) a database that uses some various collations in its columns,  which I'd like to all change to the same collation where it's not the same yet. How would I quickly and easily loop through them all and change them (or generate a script to change them)?
LVL 10
NeoTeqAsked:
Who is Participating?
 
Rajesh_mjConnect With a Mentor Commented:
Hi,

It will give the script to change the collate which is not same as database default.

declare  @toCollation sysname
     
SET    @toCollation = 'SQL_Latin1_General_CP1_CI_AS' --  Database default collate

SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +
       '   ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
       CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
            WHEN DATA_TYPE in ('text','ntext') then ''
            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
             THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
            ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
       +' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
                                           WHEN 'YES' THEN 'NULL'
                                           WHEN 'No' THEN 'NOT NULL'

END
FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES
ON  INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA  = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
AND TABLE_TYPE = 'BASE TABLE'
 and COLLATION_NAME <> @toCollation



http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22959797.html
0
 
NeoTeqAuthor Commented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.