Posted by manstein on 10/03/07 15:18

Hey all

I have written a script to change the collation of user defined
columns "en masse" in case anyone is interested:

SET NOCOUNT ON;

DECLARE @i int,
@imax int,
@Col varchar(255),
@Table varchar(255),
@SQL varchar(255)

DECLARE @Cols TABLE(
indx int IDENTITY(1,1),
ColName varchar(255),
ColType varchar(255),
ColSize varchar(255),
TableName varchar(255)
);

-- Load up the tmp table with the user defined cols
INSERT INTO @Cols (
ColName,
ColType,
ColSize,
TableName
)
SELECT
c.[name],
y.[name],
c.[max_length],
t.[name]
FROM
sys.columns c
INNER JOIN sys.tables t ON t.[object_id] = c.[object_id]
INNER JOIN sys.types y ON y.system_type_id = c.system_type_id
WHERE
t.type_desc = 'USER_TABLE' AND
y.[name] IN ( 'nchar','nvarchar','char','varchar','ntext','text' )

SELECT
@i = 1,
@imax = MAX( INDX )
FROM
@Cols;

WHILE ( @i <= @imax )
BEGIN

SELECT
@Table = '[' + TableName + ']',
@Col = '[' +
CASE
WHEN ColType IN ( 'text', 'ntext' ) THEN ColName + ']'
ELSE ColName + '] ' + ColType + '(' + ColSize + ')'
END
FROM
@Cols
WHERE
indx = @i;

SET @SQL = 'ALTER TABLE ' + @Table + ' ALTER COLUMN ' + @Col +
' COLLATE SQL_Latin1_General_CP1_CI_AS;';
PRINT @SQL
--EXEC (@SQL);

SET @i = @i + 1;
END

[Back to original message]