You can change your database collation from enterprise manager. But your columns in table did not change accordingly.
Here is show how to change all collation columns in database.
I updated the SQL 2000 Version from vbnetsample.blogspot.com
Note: The EXEC-Statment ist commented for protection. Uncomment to Execute the Printed Alter-Statemnts.
SQL 2000 Version:
Sample Script
DECLARE @@TableName Nvarchar(100)
DECLARE @@ColumnName Nvarchar(100)
DECLARE @@ColumnType NvarChar(100)
DECLARE @@ColumnLengh FLOAT
DECLARE @@SQL NvarChar(1000)
DECLARE @@IsNullAble NvarChar(50)
DECLARE my_cursor CURSOR FOR
Select sysobjects.name From sysobjects Where xtype=’u’ OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @@TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE my_column CURSOR FOR
select syscolumns.name,systypes.name as Type, syscolumns.length ,syscolumns.isnullable
From syscolumns inner join sysobjects on sysobjects.id=syscolumns.id
left outer join sysproperties on sysproperties.smallid=syscolumns.colid and sysproperties.id = syscolumns.id
inner join systypes on syscolumns.xtype = systypes.xtype
where sysobjects.xtype=’u’ And sysobjects.name=@@TableName And (systypes.name=’nvarchar’ or systypes.name=’varchar’)
OPEN my_column
FETCH NEXT FROM my_column INTO @@ColumnName,@@ColumnType,@@ColumnLengh,@@IsNullAble
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@@IsNullAble=1)
BEGIN
Select @@SQL =( ‘ALTER TABLE ‘ + @@TableName + ‘ ALTER COLUMN ‘ + @@ColumnName + ‘ ‘ + @@ColumnType + ‘(’ + CAST(@@ColumnLengh as
NVARCHAR) + ‘) COLLATE ‘ + ‘ Thai_CI_AS NULL’)
END
ELSE
BEGIN
Select @@SQL =( ‘ALTER TABLE ‘ + @@TableName + ‘ ALTER COLUMN ‘ + @@ColumnName + ‘ ‘ +
@@ColumnType + ‘(’ + CAST(@@ColumnLengh as NVARCHAR) + ‘) COLLATE ‘ + ‘ Thai_CI_AS NOT NULL’)
END
PRIN(@@SQL)
–EXEC(@@SQL)
FETCH NEXT FROM my_column INTO @@ColumnName,@@ColumnType,@@ColumnLengh,@@IsNullAble
END
CLOSE my_column
DEALLOCATE my_column
FETCH NEXT FROM my_cursor INTO @@TableName
END
CLOSE my_cursor
DEALLOCATE my_cursor
GO
SQL 2005 Version:
DECLARE @@TableName Nvarchar(100)
DECLARE @@ColumnName Nvarchar(100)
DECLARE @@ColumnType NvarChar(100)
DECLARE @@ColumnLengh FLOAT
DECLARE @@SQL NvarChar(1000)
DECLARE @@IsNullAble NvarChar(50)
DECLARE my_cursor CURSOR FOR
Select sys.objects.name From sys.objects Where [type]=’u’ OPEN my_cursor
Select object_id FROM sys.objects
Select * FROM sys.syscolumns
Select * FROM sys.extended_properties
FETCH NEXT FROM my_cursor INTO @@TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE my_column CURSOR FOR
select sys.syscolumns.name,systypes.name as Type, sys.syscolumns.length ,sys.syscolumns.isnullable
From sys.syscolumns inner join sys.objects on sys.objects.object_id=sys.syscolumns.id
left outer join sys.extended_properties on sys.extended_properties.minor_id=sys.syscolumns.colid
and sys.extended_properties.minor_id = sys.syscolumns.id
inner join systypes on sys.syscolumns.xtype = systypes.xtype
where sys.objects.type=’u’ And sys.objects.name=@@TableName And (systypes.name=’nvarchar’ or systypes.name=’varchar’)
OPEN my_column
FETCH NEXT FROM my_column INTO @@ColumnName,@@ColumnType,@@ColumnLengh,@@IsNullAble
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@@IsNullAble=1)
BEGIN
Select @@SQL =( ‘ALTER TABLE ‘ + @@TableName + ‘ ALTER COLUMN ‘ + @@ColumnName + ‘ ‘ + @@ColumnType + ‘(’ + CAST(@@ColumnLengh as
NVARCHAR) + ‘) COLLATE ‘ + ‘ Latin1_General_CI_AS_KS_WS NULL’)
END
ELSE
BEGIN
Select @@SQL =( ‘ALTER TABLE ‘ + @@TableName + ‘ ALTER COLUMN ‘ + @@ColumnName + ‘ ‘ +
@@ColumnType + ‘(’ + CAST(@@ColumnLengh as NVARCHAR) + ‘) COLLATE ‘ + ‘ Latin1_General_CI_AS_KS_WS NOT NULL’)
END
PRINT(@@SQL)
–EXEC(@@SQL)
FETCH NEXT FROM my_column INTO @@ColumnName,@@ColumnType,@@ColumnLengh,@@IsNullAble
END
CLOSE my_column
DEALLOCATE my_column
FETCH NEXT FROM my_cursor INTO @@TableName
END
CLOSE my_cursor
DEALLOCATE my_cursor