Change collation of column with User-Defined Datatype

I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?
Hi
"DBA72" wrote:

> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change the
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John