Database level setting : Allow user input chinese character~

Dear all,

To allow users enter chinese character into table, I did try to change the field type to nchar and this is workable. But I have few hundred tables.. Is there anyway to change the setting in easier way? by instead change the field type one by one for each table?

I tried change the collation to Chinese_PRC_90_BIN for the database, but it is not support chinese input..

Any Idea?

Thanks.

You will have to ALTER each table, changing the datatype from char()/varchar() to nchar()/nvarchar() on each column that will contain Chinese characters.

There is a undocumented system stored procedure, sp_MSForEachTable that may be useful in combination with using dynamic SQL.

There have been recent postings related to a similar issue, perhaps a search in the forums for 'sp_MSForEachTable', as well as a Google? search would also be useful.

|||

Hi,

you can use the following script to change every char / varchar column to contain unicode data. The statement will create ALTER statements which can be examined (in the case that you do not want to change every columns and want to sort out the appropiate ones)

SELECT
'ALTER TABLE [' + T.TABLE_NAME + ']' +
' ALTER COLUMN' + '[' + COLUMN_NAME + '] N' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)) + ');'
FROM [INFORMATION_SCHEMA].[COLUMNS] C
INNER JOIN [INFORMATION_SCHEMA].[TABLES] t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
C.TABLE_NAME = t.TABLE_NAME
WHERE data_type IN ('CHAR','VARCHAR')
AND t.TABLE_TYPE = 'BASE TABLE'

Jens K. Suessmeyer.

http://www.sqlserver2005.de