Link to home
Start Free TrialLog in
Avatar of steveLaMi
steveLaMiFlag for United States of America

asked on

Can I update the Collation value?

Ok, I am SQL newbie!
I have a sql 2005 database that somehow the COLLATION property of almost every column has changed from DATABASE DEFAULT to SQL_Latin1_General_CP850_CI_AS.  Is there a way to do a mass change of the COLLATION value to be DATABASE DEFAULT for all my columns?

I have included a screenshot of the column properties.
sqlissue.JPG
Avatar of steveLaMi
steveLaMi
Flag of United States of America image

ASKER

I think my earlier question title was not clear, so I have changed it.
Avatar of David Todd
Hi,

If most of the tables appear to have the collation changed on most columns, I'd put the question a little differently - did the default collation on the database change?

What happens is that at creation the database has either a collation specified, or picks up the server collation.

When a column is created, it has the collation specified or picks up the database collation.

So if the database gets its collation changed then the column will keep the collation it was created with.

HTH
  David
could please you estimate much string data is in the tables that you want to change.
Scrap last comment. This should do the trick. It will generate a t-sql command to alter all string columns in a database. Please also read the notes at the end.

------------------------
--START OF SCRIPT
declare @theCollationNameYouWantToUse as varchar(500)
set @theCollationNameYouWantToUse = 'SQL_Latin1_General_CP850_CI_AS'

select 'alter table dbo.[' + table_name + '] ' +
            'alter column [' + column_name + '] ' +
            data_type + '(' + cast(character_maximum_length as varchar(10)) + ') ' +  --redeclare string type length
            'collate ' + @theCollationNameYouWantToUse +
            case when is_nullable = 'NO' then ' not null' else '' end --redeclare nullability
from information_schema.columns
where collation_name is not null
--END OF SCRIPT
------------------------

1. run the above script in the database that has the problem you describe changing the variable @theCollationNameYouWantToUse to whatever collation you wish to use.
2. copy the resultant output and run that in the same database to complete the fix.
3. consider that executing each line will take a schema lock on the each table during execution. if your database has alot of transactions on large tables you may want to run the output line-by-line and when your DB is in downtime. if your DB is say <5GB with less than 100 concurrent users this is unlikely to be a concern and you can run the output in one go.
4. to be ultra cautious consider a backup of the DB before executing the output.
5. not all columns can have their collation changed : see BOL here [http://msdn2.microsoft.com/en-us/library/ms190273.aspx] and search for the text [The COLLATE clause can be used
to change the collations only of columns]
6. off course it is a little unnerving how this happened in the first place but you could check by running this command: select modify_date from sys.tables where name '[your table name]'

Hope that helps!!

Regards

PBV
ASKER CERTIFIED SOLUTION
Avatar of pbellovox
pbellovox
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
My apolgoies for teh delay in getting back.  The flu bug has set me back.
Here is what I changed your script to:

declare SQL_Latin1_General_CP1_C1_AS as varchar(500)
set SQL_Latin1_General_CP1_C1_AS = 'SQL_Latin1_General_CP850_CI_AS'

select 'alter table dbo.[' + table_name + '] ' +
            'alter column [' + column_name + '] ' +
            data_type + '(' + cast(character_maximum_length as varchar(10)) + ') ' +  --redeclare string type length
            'collate ' + SQL_Latin1_General_CP1_C1_AS +
            case when is_nullable = 'NO' then ' not null' else '' end --redeclare nullability
from information_schema.columns
where collation_name is not null

SQL_Latin1_General_CP1_C1_AS is the default collation name.  I want all columns in all tables to be changed to this.  Is there a wildcard I can use for all tables and columns when it asks me
select 'alter table dbo.[' + table_name + '] ' +
            'alter column [' + column_name + '] ' +

If I run it now, I get the error
 "Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
local tsql variables are identfied by the prefix "@" which is all that is missing here. Try this:

declare @SQL_Latin1_General_CP1_C1_AS as varchar(500)
set @SQL_Latin1_General_CP1_C1_AS = 'SQL_Latin1_General_CP850_CI_AS'

select 'alter table dbo.[' + table_name + '] ' +
            'alter column [' + column_name + '] ' +
            data_type + '(' + cast(character_maximum_length as varchar(10)) + ') ' +  --redeclare string type length
            'collate ' + @SQL_Latin1_General_CP1_C1_AS +
            case when is_nullable = 'NO' then ' not null' else '' end --redeclare nullability
from information_schema.columns
where collation_name is not null