Solved

change collation on all varchar fields in database

Posted on 2004-09-09
10
673 Views
Last Modified: 2012-06-21
how can i do this?

is Something like this possible:

'update varchar field set collation = DANISH_NORWEGIAN Where varchar field in (select all varchar fields in all tables in database)'
0
Comment
Question by:Darth_helge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12015119
You might find some scripts that try to do this, but there are TOO many things that won't work in a automated way.
The preferred method is to create a new database with all the new collations in place, and copy over the database data.
This is a job you won't run many times on a database...
see here:
http://support.microsoft.com/?kbid=325335#7

CHeers
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12015141
the following SQL will generate ALTER statements for you, which will work in the simplest of cases.
However, as angelIII says, a lot can go wrong - particularly, you cannot alter collation on an indexed column, or a column that has a FK relationship. If you have 1 or 2 indexes, try dropping them and recrating them afterwards.  If you have a lot of these, you're probably best recreating the whole database, as stated.

select 'alter table [' + table_schema + '].[' + table_name + '] alter column [' +
column_name + '] ' + data_type + '(' + convert(varchar(4),character_maximum_length) + ')' +
'collate DANISH_NORWEGIAN_CI_AS' +
case when is_nullable = 'YES' then ' NULL' else ' NOT NULL' end
from information_schema.columns
where data_type in ('varchar','char', 'nvarchar', 'nchar')
0
 
LVL 1

Expert Comment

by:ratb3rt
ID: 12015201
This should work (please excuse my shoddy sql ;)



declare @tabname sysname, @colname sysname, @collen int, @colnull int
declare @sqlstr nvarchar(1000)
declare mycur cursor for
 select so.name,sc.name, sc.length, sc.isnullable from sysobjects so (nolock), syscolumns sc (nolock),systypes st (nolock)
 where so.type='U' and sc.id=so.id and st.xtype=sc.xtype and st.name='varchar'
open mycur
fetch next from mycur into @tabname, @colname, @collen, @colnull
while @@fetch_status=0
begin
 set @sqlstr='ALTER TABLE '+rtrim(@tabname)+' ALTER COLUMN '+rtrim(@colname)+
            ' varchar('+rtrim(@collen)+')COLLATE DANISH_NORWEGIAN '
 if @colnull=1
      set @sqlstr=@sqlstr+' NULL'
 else
   set @sqlstr=@sqlstr+' NOT NULL'
print @sqlstr
 exec sp_executesql @sqlstr
 fetch next from mycur into @tabname, @colname, @collen, @colnull
end
close mycur
deallocate mycur


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:Darth_helge
ID: 12015232
ok, I tried creating a new database, and then copy the data from the old to the new one, but it failed. Both the choices copy tables and views and copy objects and data, failed.
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 500 total points
ID: 12015306
What failed? What was the error message? How did you create the database? You need to create a database, and all the teables etc, no good just creating an empty database, then copying the objects across.
The best way to create the database is to script out the existing database (Enterprise Manager All tasks -> generate SQL script) , then edit the script to replace the collation with what you want, (and change the database name / filenames!) then run this script in QA.
0
 
LVL 5

Author Comment

by:Darth_helge
ID: 12015332
ok, I will try that thanks
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12015408
>>Both the choices copy tables and views and copy objects and data, failed.
this is obvious because the collections between the databases differ.
As BillAn1 indicates, first create the objects with the new collation, and then copy the data
CHeers
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

729 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