Change from char to varchar and RTRIM

Hi,
I have a big database with most of the columns having the datatype char
(char(10), (char(50) etc.)
I would like to change all these columns to varchar (varchar(10),
varchar(50), ...), and have all the spaces on the right trimmed of. So
instead of 'ABC ' I want to have 'ABC'.
Is there a 'nice' way to do this? Changing all the char-columns to varchar
doesn't seem too difficult to me, but would there be a way that automaticly
cuts of this trailing spaces? Or is there some script that exists that does
this for me?
The biggest problem in my opinion is that some of these columns are used as
foreign keys. So I don't know if it is possible that they will be trimmed in
one table, but not yet in the foreign-key-table?
Any help our hints would be really appreciated!
Thanks a lot in advance,
PieterHello, Pieter
To change the data types of the columns, you need to drop the foreign
keys, change all the columns involved (using "ALTER TABLE tbl ALTER
COLUMN col varchar(n)") and then recreate the foreign keys. That's
because a foreign key requires the referencing columns to have the same
data type as the referenced columns (additionally, a column involved in
any kind of constraint cannot be altered).
The trimming can be done before or after recreating the foreign keys,
because when SQL Server compares 'a' with 'a ', they will be equal. For
example, the following would work just fine:
USE tempdb
CREATE TABLE t1 (x varchar(10) primary key)
CREATE TABLE t2 (y varchar(10) references t1)
INSERT INTO t1 VALUES('a')
INSERT INTO t2 VALUES('a ')
SELECT x+'!', y+'!' FROM t1 INNER JOIN t2 ON x=y
DROP TABLE t2,t1
Razvan