CODEWITHSUNDEEP
sqlsql-servercollections
Sasha
Sasha

Reputation: 20824

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

I need to reset collation for all columns in all tables in the database: enter image description here

I want to use default collation of database

I tried to change it under database properties: enter image description here

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

anybody has script that can do it for me?

Upvotes: 6

Views: 6936

Answers (4)

Mohamed Nagieb
Mohamed Nagieb

Reputation: 849

A proposed script (can be found here) is meant to iterate through all tables in your DB and changing the collation to the desired one.

It is based on the script: 

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

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

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.

Upvotes: 3

Dan D.
Dan D.

Reputation: 74645

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

ALTER DATABASE database_name COLLATE collation_name

Upvotes: 1

Angshuman Bhattacharjee
Angshuman Bhattacharjee

Reputation: 415

The fastest way to reset a column would be to SET UNUSED the column, then add a column with the same name and datatype.

This will be the fastest way since both operations will not touch the actual table (only dictionary update).

The actual ordering of the columns will be changed (the reset column will be the last column). If your code rely on the ordering of the columns (it should not!) you can create a view that will have the column in the right order (rename table, create view with the same name as old table, revoke grants from base table, add grants to view).

The SET UNUSED method will not reclaim the space used by the column (whereas dropping the column will free space in each block).

Hope this helps.

Upvotes: 0

Related Questions