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
.

6 comments:

Anonymous said...

Nice script and very usefull. Thanks a lot!

Unknown 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?

Anonymous said...

Right here is the right site for anybody who really wants to find out
about this topic. You know so much its almost hard to argue with you (not that
I personally will need to…HaHa). You definitely
put a fresh spin on a topic that's been written about for decades.
Wonderful stuff, just great!

Feel free to surf to my web blog comparador de apuestas - http://love-da-records.it-factory.com.hk/forum/home/space.php?uid=452211&do=blog&id=435754 -

Anonymous said...

fear of god
golden goose
yeezy
supreme hoodie
off white
chrome hearts outlet
bape
kyrie shoes
off white outlet
kyrie 9