• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • Last Modified:

change collation for multiple columns at one time - sql

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?
0
CeleritasPrime
Asked:
CeleritasPrime
1 Solution
 
ValentinoVBI ConsultantCommented:
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 '
	+ REPLACE(REPLACE(IS_NULLABLE, 'YES', 'NULL'), 'NO', 'NOT NULL')
from INFORMATION_SCHEMA.COLUMNS
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...
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now