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 [at] i int,
[at] imax int,
[at] Col varchar(255),
[at] Table varchar(255),
[at] SQL varchar(255)
DECLARE [at] 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 [at] 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
[at] i = 1,
[at] imax = MAX( INDX )
FROM
[at] Cols;
WHILE ( [at] i <= [at] imax )
BEGIN
SELECT
[at] Table = '[' + TableName + ']',
[at] Col = '[' +
CASE
WHEN ColType IN ( 'text', 'ntext' ) THEN ColName + ']'
ELSE ColName + '] ' + ColType + '(' + ColSize + ')'
END
FROM
[at] Cols
WHERE
indx = [at] i;
SET [at] SQL = 'ALTER TABLE ' + [at] Table + ' ALTER COLUMN ' + [at] Col +
' COLLATE SQL_Latin1_General_CP1_CI_AS;';
PRINT [at] SQL
--EXEC ( [at] SQL);
SET [at] i = [at] i + 1;
END
