1
Hello all... I'm new here so bare with me please. I need to change collation type for a ton of tables and columns. I found a great site that showed me how to generate the sql to do this using a sql statement. This is the sql statement I'm using to generate it.
declare @fromCollation sysname,
@toCollation sysname
set @fromCollation = 'SQL_Latin1_General_CP1_CS_AS'
set @toCollation = 'SQL_Latin1_General_CP1_CI_AS'
SELECT 'ALTER TABLE ' + TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME + ' ' + 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 like @fromCollation
This came from the sql doctor.
On to my question. I use this sql and get a nice statement from it to change all relevant columns. The problem starts when I issue the alter table statement. I start to get errors because I have constraints that depend upon these columns. What I've read so far is that I have to remove these constraints and recreate them. This is impossible to do by hand, so I thought I'd work on writing a sql statement that'd do it for me. Using bits of code I've pulled from here I think I understand the process but I definitely don't want to delete all these constraints just to have the sql not work to recreate them. Does anyone have experience doing this type of thing? Thanks for the help!
Jerel
