Change collation for a DB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Change collation for a DB

Author

Topic

nagarjuna.raju
Starting Member

1 Post

Posted - 2011-07-04 : 08:25:06

Hi,

Can someone help to change the Collation of a database(I have to change the collation of all tables and for each column in the tables in the Database).

i want to change the collation to a different one from the default collation for a single database.

Thanks.

mikgri
Starting Member

39 Posts

Posted - 2011-07-05 : 15:24:18

You can change collation of database using sql server management studio:
right click on database -> properties -> choose options on the right pane and choose collation from drop down list on the right pane, or
execute the following statement:
use master;
go
alter database <dbname> collate <new collation name>
go

Regards.

SpikeMelbost
Starting Member

6 Posts

Posted - 2011-07-05 : 18:05:13

I have been fighting a collation issue for a while.

I'm new to SQL, so please accept my apologies if this is incorrect, and if so maybe someone will correct me.

The suggestion above will I think change the default collation for the database, but not for any existing data.

I have a situation where I have existing data with the wrong collation. I have changed the collation for the database & the server, but the column level collation for my existing tables is wrong.

If I export to a new database, the collation is correct, but the new database has no stored procedures (and probably is missing other things?), so thats no good.

I thought I could maybe drop & create the database then import the data. Maybe I'm doing it wrong or thats not valid. When I try to drop the database I get an error because its in use. When I try to import I get an error because a field is read only.

I've gone back to looking at writing a small program to use UPDATE TABLE UPDATE COLUMN statements to change collation.
This is not straight forward & I'm not sure it'll work, but may know soon.

SpikeMelbost
Starting Member

6 Posts

Posted - 2011-07-06 : 04:11:50

oops, I meant ALTER TABLE ALTER COLUMN

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-07-06 : 08:01:59

Changing collation for the entire database is a MAJOR PAIN IN THE A$$! The easiest is probably to create a brand new database with the desired collation, script out all tables and other database objects removing the collation settings for each object, and then move all the data to the new database.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

SpikeMelbost
Starting Member

6 Posts

Posted - 2011-07-06 : 19:45:54

"script out all tables and other database objects removing the collation settings for each object, and then move all the data to the new database"

I've not had any success trying to do this. Maybe its down to a lack of experience.

I have, however, finally got my database and data into the correct collation using ALTER TABLE ALTER COLUMN - or so it seems anyway. The page that fell over with a collation conflict previously is now working, and a quick test has not shown up any problems, so fingers crossed ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources