how do I reset collation for all columns in the database?

Question

I need to reset collation for all columns in all tables in the database:

I want to use default collation of database

I tried to change it under database properties:

but collation already setted in columns and it mean that i cannot overwrite it

anybody has script that can do it for me?

Answer 1

I've knocked together a script that should do a decent enough job (hopefully). Run the script in the appropriate database, with results as text. Then Copy & Paste the output into a script window to change the collation of each column:

declare @NewCollationName sysname
set @NewCollationName = 'Latin1_General_CS_AS'
select
    'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(st.schema_id)) + '.' + QUOTENAME(st.name) +
    ' ALTER COLUMN ' + QUOTENAME(sc.name) + ' ' + styp.name + '(' +
    CASE WHEN sc.max_length = -1 THEN 'max' ELSE CONVERT(varchar(10),sc.max_length) END +
    ') collate ' + @NewCollationName + '
    go
    '
from
    sys.columns sc
        inner join
    sys.tables st
        on
            sc.object_id = st.object_id
        inner join
    sys.types styp
        on
            sc.user_type_id = styp.user_type_id
where
    sc.collation_name is not null and
    OBJECTPROPERTY(st.object_id,N'IsMSShipped')=0

One thing to note, however, is that the generated script won't work if the columns are the target of constraints or targetted by a schema bound object (view or function).

In such cases, you'd have to script out the dependent objects, drop them from the database, then run the script generated by the script above, and finally re-add the dependent objects.

Answer 2

See (Changing the Database Collation) http://msdn.microsoft.com/en-us/library/ms174269.aspx

ALTER DATABASE database_name COLLATE collation_name