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
steveLaMiAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
pbellovoxConnect With a Mentor Commented:
I negelect to mention.... to get the name of the default collation for the server run following query: select serverproperty('Collation')
0
 
steveLaMiAuthor Commented:
I think my earlier question title was not clear, so I have changed it.
0
 
David ToddSenior DBACommented:
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
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
pbellovoxCommented:
could please you estimate much string data is in the tables that you want to change.
0
 
pbellovoxCommented:
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
0
 
steveLaMiAuthor Commented:
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'.
0
 
pbellovoxCommented:
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

0
All Courses

From novice to tech pro — start learning today.