Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Change Existing Field Collation Expand / Collapse
Author
Message
Posted Tuesday, October 07, 2008 12:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 30, 2013 6:40 AM
Points: 2, Visits: 25
How can I change the collation of one existing database ? I want to change also all existing fields not only the database collation.

In SQL 2000 in properties was a flag "Allow modifications to be made directly to the system catalogs". This not exists in 2005.
Post #581533
Posted Wednesday, October 08, 2008 7:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 06, 2011 11:54 AM
Points: 154, Visits: 203
Use "alter database" and "alter table" commands with COLLATE clause.
Post #583020
Posted Thursday, October 09, 2008 2:12 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:27 AM
Points: 925, Visits: 1,094
Had the same issue, try this script for fixing collation in your tables:

------------------------------------------------------------
-- LM_ChangeCollation - Change collation in all tables
-- made by Luis Monteiro - ljmonteiro@eurociber.pt
-- modified by wilfred van dijk - wvand@wilfredvandijk.nl
------------------------------------------------------------

DECLARE @new_collation varchar(100)
DECLARE @debug bit

DECLARE
@table sysname,
@previous sysname,
@column varchar(60),
@type varchar(20),
@legth varchar(4),
@nullable varchar(8),
@sql varchar(8000),
@msg varchar(8000),
@servercollation varchar(120)

/*
uncomment one of the following lines:
*/
set @new_collation = convert(sysname, databasepropertyex(DB_NAME(), 'collation'))
-- set @new_collation = convert(sysname, serverproperty('collation'))

/*
@debug = 0 to execute
*/
set @debug = 1

if @new_collation is null
begin
print 'which collation?'
goto einde
end

DECLARE C1 CURSOR FOR
select 'Table' = b.name,
'Column' = a.name,
'Type' = type_name(a.system_type_id),
'Length' = a.max_length,
'Nullable' = case when a.is_nullable = 0 then 'NOT NULL' else ' ' end
from sys.columns a
join sysobjects b
on a.object_id = b.id
where b.xtype = 'U'
and b.name not like 'dt%'
and type_name(a.system_type_id) in ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
and a.[collation_name] <> @new_collation
order by b.name,a.column_id

OPEN C1
FETCH NEXT
FROM C1
INTO @table,@column,@type,@legth,@nullable
set @previous = @table

WHILE @@FETCH_STATUS = 0
BEGIN
if @table <> @previous print ''
set @sql = 'ALTER TABLE ' + QUOTENAME(@table) + ' ALTER COLUMN ' + QUOTENAME(@column) + ' '
set @sql = @sql + @type + '(' + @legth + ')' + ' COLLATE ' + @new_collation + ' ' + @nullable

print @SQL

if @debug = 0
begin
begin try
EXEC (@sql)
end try
begin catch
print 'ERROR:' + ERROR_MESSAGE()
print ''
end catch
end

set @previous = @table
FETCH NEXT
FROM C1
INTO @table,@column,@type,@legth,@nullable
END
CLOSE C1
DEALLOCATE C1

einde:

Note: You'll manually have to fix columns which are part of a key/constraint (by using management studio)


Wilfred
The best things in life are the simple things
Post #583120
Posted Wednesday, October 15, 2008 12:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 30, 2013 6:40 AM
Points: 2, Visits: 25
thanks. I will try this script.
Post #586443
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse