Solved

Script to change collation

Posted on 2007-11-26
3
3,264 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQLCMD Syntax 2 16
SQL Find Carriage Return and Delete it. 3 16
SQL Recursion schedule 13 19
Evaluate Twice? 2 9
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Viewers will learn how the fundamental information of how to create a table.

828 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