We help IT Professionals succeed at work.

case sensitive database on sql server 2008 r2

patd1
patd1 asked
on
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.
Comment
Watch Question

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Maybe your individual tables or individual fields are case sensitive.

Best way is to script out each table and examine them for CS.

Author

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.

Author

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 Engineer
Distinguished Expert 2017
Commented:
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

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ãoIT Engineer
Distinguished Expert 2017

Commented:
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?

Author

Commented:
There are several tables with multiple indexes. Dropping and recreating manually will take hours.

Author

Commented:
I was able to script out all indexes, dropped them, changed collation with the script provided above and the recreated all indexes.

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
Yeah, I wanted to tell you that for script all indexes.
Good that you could found it by yourself.
Cheers.