October 7, 2008 at 12:26 am
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.
October 8, 2008 at 7:18 pm
Use "alter database" and "alter table" commands with COLLATE clause.
October 9, 2008 at 2:12 am
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
@tablesysname,
@previoussysname,
@column varchar(60),
@typevarchar(20),
@legthvarchar(4),
@nullablevarchar(8),
@sqlvarchar(8000),
@msgvarchar(8000),
@servercollationvarchar(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
fromsys.columns a
joinsysobjects b
ona.object_id = b.id
whereb.xtype = 'U'
andb.name not like 'dt%'
andtype_name(a.system_type_id) in ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
anda.[collation_name] <> @new_collation
orderby b.name,a.column_id
OPENC1
FETCHNEXT
FROMC1
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
FETCHNEXT
FROMC1
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
October 15, 2008 at 12:17 pm
thanks. I will try this script.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply