case sensitive database on sql server 2008 r2

Using SQL server 2008 R2.

The collation_name  for my database is set to SQL_Latin1_General_CP1_CI_AS, but my database is still case sensitive.
What else do I need to do to make it case insensitive?

There are other databases on the same instance, that have the same collation_name and are case insensitive.

Thanks.
patd1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Maybe your individual tables or individual fields are case sensitive.

Best way is to script out each table and examine them for CS.
patd1Author Commented:
I scripted out my table which I was querying when I found that the db was set to be case sensitive.
But, the script does not show and collation.

I checked the extended properties on the table and it shows SQL_Latin1_General_CP1_CI_AS, which means it should be case insensitive, but its not.
patd1Author Commented:
I just ran the following sql to change the collation of a column and now this works as case insensitive.
Is there any way I can change the case sensitivity for the entire database (all tables and columns )
  ALTER TABLE my_table
ALTER COLUMN my_col VARCHAR(250)
COLLATE SQL_Latin1_General_CP1_CI_AS

Open in new window



The following query that I ran earlier seemd to change the collation for the db, but still individual columns on tables are case sensitive.

Alter database my_db
COLLATE  SQL_Latin1_General_CP1_CI_AS

Open in new window

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Vitor MontalvãoMSSQL Senior EngineerCommented:
If you change a SQL Server instance collation it won't modify the databases collation as the same way when you change a database collation it won't change the columns collations. You need to it by yourself.
Check if the following script solve your problem:
DECLARE @TABSCHEMA VARCHAR(128)
DECLARE @TABNAME VARCHAR(128)
DECLARE @COLNAME VARCHAR(128)
DECLARE @COLDATATYPE VARCHAR(128)
DECLARE @COLLEN VARCHAR(128)
 
DECLARE @AlterCommand NVARCHAR(4000)

-- Find the columns that has the old collation
DECLARE TABLE_COLS CURSOR FOR
SELECT C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS C
	INNER JOIN INFORMATION_SCHEMA.TABLES T
		ON T.TABLE_CATALOG = C.TABLE_CATALOG AND
			T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
			T.TABLE_NAME = C.TABLE_NAME
WHERE C.COLLATION_NAME = 'SQL_Latin1_General_CP1_CI_AS' 
	AND T.TABLE_TYPE = 'BASE TABLE'

OPEN TABLE_COLS
FETCH NEXT FROM TABLE_COLS INTO @TABSCHEMA, @TABNAME, @COLNAME, @COLDATATYPE, @COLLEN

WHILE @@FETCH_STATUS=0
	BEGIN
		-- Change the column to the new collation
		SET @AlterCommand = N'ALTER TABLE ' + @TABSCHEMA + '.' + @TABNAME + ' ALTER COLUMN ' + @COLNAME + ' ' + @COLDATATYPE + '(' + @COLLEN + ') COLLATE SQL_Latin1_General_CP1_CI_AS'
		--PRINT @AlterCommand 
		EXECUTE sp_executesql @AlterCommand 
		FETCH NEXT FROM TABLE_COLS INTO @TABSCHEMA, @TABNAME, @COLNAME, @COLDATATYPE, @COLLEN 
	END

CLOSE TABLE_COLS
DEALLOCATE TABLE_COLS

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
patd1Author Commented:
I am getting error about indexes being dependent on the columns that I am trying to change collation on using the script above. How do I first change collation on all indexes on this db?

BTW, i also changed the code below, as I want to find columns that Do Nott have case insensitive collation and then change it to case insensitive.
WHERE C.COLLATION_NAME = 'SQL_Latin1_General_CP1_CI_AS' 

Open in new window

to
WHERE C.COLLATION_NAME <> 'SQL_Latin1_General_CP1_CI_AS'

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, changing columns that are indexed will be a problem since you need to drop the index, change the column and at the end recreate the index.
How many of those cases you've found?
patd1Author Commented:
There are several tables with multiple indexes. Dropping and recreating manually will take hours.
patd1Author Commented:
I was able to script out all indexes, dropped them, changed collation with the script provided above and the recreated all indexes.
patd1Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for Vitor Montalvão's comment #a40635208
Assisted answer: 0 points for patd1's comment #a40635648

for the following reason:

done. Thanks for your help Vitor Montalvão.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yeah, I wanted to tell you that for script all indexes.
Good that you could found it by yourself.
Cheers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.