Solved

Script to change collation

Posted on 2007-11-26
3
3,262 Views
Last Modified: 2011-09-20
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)?
0
Comment
Question by:NeoTeq
3 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 20349370
0
 
LVL 6

Accepted Solution

by:
Rajesh_mj earned 500 total points
ID: 20349443
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
 
LVL 10

Author Comment

by:NeoTeq
ID: 20349732
Thanks!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question