Swiped from DrSQL
http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1333.entry
use [database]
go
Declare @fromCollation sysname
, @toCollation sysname
SET @fromCollation = ‘SQL_Latin1_General_CP1_CI_AS’ –or whatever
SET @toCollation = ‘SQL_Latin1_General_CP1_CI_AS’ –or whatever
SELECT ‘ALTER TABLE ‘ + quotename(TABLE_NAME)
+ ‘ ALTER COLUMN ‘ + quotename(COLUMN_NAME) + ‘ ‘ + quotename(DATA_TYPE)
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then ‘(max)’
WHEN DATA_TYPE in (‘text’,’ntext’) then ”
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN ‘(‘+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+’)’ )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),’ ‘) END
+ ‘ COLLATE ‘ + @toCollation+ ‘ ‘ + CASE IS_NULLABLE
WHEN ‘YES’ THEN ‘NULL’
WHEN ‘No’ THEN ‘NOT NULL’ END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN (‘varchar’ ,’char’,’nvarchar’,’nchar’,’text’,’ntext’)
AND COLLATION_NAME not like @toCollation