Thursday, February 10, 2011

Database Collation Change

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