Change case sensitivity of database

[johnv20]

Hi,
is there any way to change the case sensitivity
(from case sensitive to case insensitive) for SQL2000?

 

[mrdenny]

SQL server is not case sensitive.

select * from sysobjects
where name like 'SYSObjecTs'

The above statement will return one record.

Denny

 

[SQLBill]

Denny's statement isn't true.

You can set case sensitivity. It's known as COLLATION.

First run this:

SELECT CASE
WHEN ('A' = 'a') THEN 0
ELSE 1
END

That will tell you whether your environment is case sensitive (1) or not case sensitive (0).

Then run ALTER DATABASE to set the collation to what you want it to be. Refer to the Books OnLine for more information. Use the Index tab and enter:
case-sensitive instance of SQL Server

-SQLBill

 

[SQLBill]

I should have added:

The information in case-sensitive instance of SQL Server is for an initial install. You need to use ALTER DATABASE if it's already installed.

Run this command to see what collations are available:

SELECT *
FROM ::fn_helpcollations()

Then use whichever one you want in an ALTER DATABASE command.

-SQLBill

 

[mrdenny]

Bill,
Apparently I wasn't thinking that all the way through. Thanks for correcting me.


Denny

 

[Luvsql]

I've tried this on one of our binary databases (ie ALTER DATABASE X SQL_Latin1_General_CP1_CS_AS) and it fails on about 2100 objects (check contraint objects).

Also, if this was successful, would it actually make the table names and field names as well as the data case-insentive ie you could do a select query on any case for the table, field and data and it would yield results where in case-sensitive, it would not?

I was told the only way to truly change a database to case-insensitive is to either DTS into a case-insentive db or use bcp becuase the indexes do not get changed thus queries won't change.

 

[johnv20]

Luvsql - that's exactly what I'm seeing & what I feared
changing collation will not change the case sensitivity
of table names etc.

Looks like its time for a rebuild

 

[pabloj]

Note that SQL_Latin1_General_CP1_CS_AS is CS (case sensitive) and AS (accent sensitive)
You have to alter database to the right collation and then update all columns (from syscolumns) to the right collation.

Stick to your guns