How to disable COLLATE in a table?

Question

How to disable COLLATE in a table?

sharma-691993

SSCrazy

Points: 2194
More actions
May 6, 2008 at 10:37 am

#121941

Hi
I had created a table with COLLATE as LATIN..
Now i am in need of collate.. I want to drop or disable the COLLATE for that particluar table..
Regards

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

SanjayAttray SSChampion Points: 13157 More actions · Answer 1

sharma (5/6/2008)
Hi
I had created a table with COLLATE as LATIN..
Now i am in need of collate.. I want to drop or disable the COLLATE for that particular table..
Regards

First I did not get your question. Second, I tried to change collation property of one of my table and you can change it explicitly. Check BOL for alter table statement.

or

create a temporary table with collation you want -> copy the data in temporary table -> drop the original table and then rename the temporary table to original.

SQL DBA.

sharma-691993 SSCrazy Points: 2194 More actions · Answer 2

Not a table level.

In table one column is in COLLATE constraint.

How to disable it?

Thanks

SanjayAttray SSChampion Points: 13157 More actions · Answer 3

Try this.

ALTER TABLE tablename

ALTER COLUMN columnname COLLATE default

SQL DBA.

sharma-691993 SSCrazy Points: 2194 More actions · Answer 4

Thanks SanjayAttray

I did

ALTER TABLE NVARCHARTBL

ALTER COLUMN NAME COLLATE default

The above query is correct?

Bcoz It shows error

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'COLLATE'.

Regards

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 5

sharma (5/8/2008)
Thanks SanjayAttray
I did
ALTER TABLE NVARCHARTBL
ALTER COLUMN NAME COLLATE default
The above query is correct?
Bcoz It shows error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'COLLATE'.
Regards

ALTER COLUMN NAME COLLATE database_default

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

sharma-691993 SSCrazy Points: 2194 More actions · Answer 6

Hi.. Same Error...

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'COLLATE'.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 7

Sorry - I forgot that the less than and greater than symbols are for html tags so it come out right

ALTER TABLENAME

ALTER COLUMN COLUMN NAME COLLATE database_default

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 8

I got the same error while running this script.

I have no problem in changing the collation in GUI but I do have problem in changing it using script in both SQL 2000/2005.

sharma-691993 SSCrazy Points: 2194 More actions · Answer 9

Hi

Can you provide the clear script..

Bcoz same error.

jaypee_s Right there with Babe Points: 793 More actions · Answer 10

[font="Comic Sans MS"][font="Comic Sans MS"]Hi

This link may be useful to you...

http://msdn.microsoft.com/en-us/library/ms190920.aspx

http://blogs.msdn.com/ikovalenko/archive/2006/12/03/alter-database-alter-collation-forced.aspx%5B/font%5D

Thanks

jaypee.s[/font]

Pablo Benito Grasshopper Points: 17 More actions · Answer 11

Hi!

There is a script to change the collation of an entire database at this link:

http://www.sqlservercentral.com/scripts/Miscellaneous/30471/[/url]

The correct syntax may be like this:

ALTER TABLE tablename

ALTER COLUMN columnname type (length) COLLATE database_default

I've tried this:

ALTER TABLE TableName

ALTER COLUMN ColumnName varchar (50) COLLATE database_default

and it works

John Burgess-239089 SSC Veteran Points: 222 More actions · Answer 12

I have the exact same issue today. I need to change the collation setting on approx 500 columns and was looking for a way to do this quickly. I found a script on a forum that will update the collationid in syscolumns but it is a backdoor method so I will not post it here.

You can use ALTER table and I have tested this. BUT, I think you must specify the datatype of the column and the Allow Nulls attribute also.

So for example :

ALTER TABLE <table> ALTER COLUMN <column> <type> COLLATE <collation> <allow_nulls>

ALTER TABLE jb_test ALTER COLUMN jb_id varchar(30) COLLATE Latin1_General_BIN NOT NULL

This works but is a pain because you need to specify those other attributes and therefore you have to first query the database to check what those attributes need to be set to for each column you want to alter. Also, if you have indexes or primary keys defined on those columns then the statement fails. This is all handled neatly by the management Studio which is why I have resorted to a manual update. Hope this helps.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

John Burgess-239089 (11/17/2009)
I found a script on a forum that will update the collationid in syscolumns but it is a backdoor method so I will not post it here.

I would suggest you don't try and run it. That's the kind of thing that results in nasty, hard-to-fix schema corruption. Besides, it won't work at all on SQL 2005. In 2005 and above, updates are not allowed at all to the system tables (besides which, the system tables are hidden) and syscolumns is just a view

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass