change collation for multiple columns at one time - sql

Posted on 2012-09-13
Last Modified: 2012-09-26
Good Morning experts,

We have 2 sql servers each with multiple databases, one server is 2008 and the other server is 2005.  the databases on the  2008 server have a different collation than the ones on the 2005 server.  

I did the following for the databases on the 2008 server

ALTER DATABASE [finance] COLLATE SQL_Latin1_General_Pref_CP1_CI_AS

It changed the database and the table collations, however the column collation remained
unchanged.  I know I can go in and changed the column collation one by one  but that is not a feasible solution.

is there a way I can change the collation for all the columns at once?
Question by:CeleritasPrime
    1 Comment
    LVL 37

    Accepted Solution

    There's no command to change the collation of all columns in one go.  For each column you'll have to execute an ALTER TABLE statement.

    However, this doesn't mean you have to type all those ALTER statements yourself.  You could create a query that generates the statement for you.

    Check out the following statement:

    select 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME 
    	+ ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '('
    	+ CONVERT(varchar(10), CHARACTER_MAXIMUM_LENGTH) + ') COLLATE YourNewCollation '
    where COLLATION_NAME = 'YourCurrentCollation'

    Open in new window

    (replace YourCurrentCollation and YourNewCollation with the correct collation names)

    If you run that on your database, you'll get an ALTER TABLE statement for each column.  These statements can then be copied from the results pane into another window, to be executed on your database.

    Ensure you have a good backup before doing this...

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    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.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now