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
.