Changing the case-sensitivity of DBs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Changing the case-sensitivity of DBs

Author

Topic

Cornelius19
Starting Member

30 Posts

Posted - 2008-08-22 : 10:36:05

Hi,

I have several case-insensitive DBs (SQL_Latin1_General_CP1_CI_AS). By default, I need to run case sensitive queries so I would like to change the DB and all the tables it contains (including tables that will be added to it in the future) to SQL_Latin1_General_CP1_CS_AS. Is there any way to do it?

If it is not possible, even creating case sensitive DBs and copying all data there would do. I could do it table by table but that would be very long (dozens of DBs with hundreds of tables in each). Is there a way to copy an entire database?

I know that even in a case insensitive DB it is possible to run case sensitive queries but that would only be a temporary solution and I need a permanent one.

Cornelius

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-22 : 11:31:39

You know the pain you will go through by changing database from CI to CS:

This article explains pretty well:

http://support.microsoft.com/default.aspx/kb/325335

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-25 : 04:57:43

Can you get away with adjusting your queries with COLLATE?


DECLARE @tableA TABLE (
	[fieldA] VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS
	)

DECLARE @tableB TABLE (
	[fieldA] VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS
	)

INSERT @tableA
      SELECT 'Mixed'
UNION SELECT 'CASE'
UNION SELECT 'entries'

INSERT @tableB
      SELECT 'MIXED'
UNION SELECT 'caSe'
UNION SELECT 'enTRIEs'

-- With case Insensitivity
SELECT
	*
FROM
	@tableA a
	JOIN @tableB b ON a.[fieldA] = b.[fieldA]

-- Case sensitivity
SELECT
	*
FROM
	@tableA a
	JOIN @tableB b ON a.[fieldA] = b.[fieldA] COLLATE SQL_Latin1_General_CP1_CS_AS

-------------
Charlie

Cornelius19
Starting Member

30 Posts

Posted - 2008-08-25 : 17:04:51

sodeep,

Thanks, that is a very relevant link.

Charlie,

Yes, that could be a temporary solution but since most of my queries would require COLLATE, it is not really viable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources