How do I sort reset for all columns in a database?

Question

I need to reset sorting for all columns in all tables in a database:

I want to use standard database sorting

I tried changing it in the database properties:

but the mapping is already set in the columns, and that means that I cannot overwrite it

Does anyone have a script that can do this for me?

Answer 1

I shot down a script together that should do pretty decent work (hopefully). Run the script in the appropriate database with the results as text. Then copy and paste the output into the script window to change the sorting 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

, script , ( ).

script , , script, script , , , .

Answer 2

. ( ) http://msdn.microsoft.com/en-us/library/ms174269.aspx

ALTER DATABASE database_name COLLATE collation_name

Answer 3

reset - UN UNEDED , .

, ( ).

( reset ). ( !), , ( , , , , ).

SET UNUSED , ( ).

, .

Answer 4

script ( ) .

script:

ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME varchar(100) COLLATE Latin1_General_CI_AS NULL