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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy