2007-07-13

Change All Column's Collation By Sql Script

Change All Column's Collation By Sql Script

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. (You can change Thai_CI_AS to your collation.)


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
.

4 comments:

Anonymous said...

Nice script and very usefull. Thanks a lot!

Rob said...

Watch this one. I think the datatype length is off.

Anonymous said...

It was a very nice idea! Just wanna say thank you for the information you have shared. Just continue writing this kind of post. I will be your loyal reader. Thanks again.alfred angelo

Anonymous said...

Conseils tres interessants. A quand la suite?