Changing Collations...

I have looked thru the forum but have a couple of questions:
1) data base was created with case insensitive collation
2) all the tables were then create (72 tables) and by default got the CI collation on all columns
3) lots of data was added 2GB
4) discovered mistake and altered the database to have case sensitive collation
5).... how to change all the collations for all the columns without doing them all manually
can i backup the database and change some settings and restore it?
export all the data, drop and recreate tables and import data?
??Why would you want case sensitive?

99.999% of every database I work with use the default out of the box collation.

It's only when I deal with 3rd party vendors that I have to mess with a different one.

This makes life painful at times.|||and the database has to be case sensitive because the customer wants to be able to use lower and upper case codes in some of the files.|||Case insensetive doesn't mean you can't have mixed case, it just means that 'a' = 'A'. That would be false in Case Insesitive.

I would leave it alone.|||fwiw i've used binary sort order (case-sensitive) for vldb implementations that use integer keys for joins and gotten tested 30% gains in join performance.|||The apps being used for the database is an OLD cobol based programs. all thru the programs we have tests for lower and upper case values that must NOT equate 'a' as equal to 'A'. so that is why i MUST change the collation. it is not an option to change anything else about the database/tables.
anyways, i was hoping that someone here might know of a better way or have a tool that does it.|||I know you can not do a restore...

And you should probably create an instance on the box that is set up the way you want it.

Script out the database, create it in that instance, and bcp everything out and back in. Use Native format

If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
@.dbName sysname
, @.fp varchar(255)
, @.User varchar(255)
, @.Pwd varchar(255)
AS
/*
EXEC isp_bcp_out_database
'Northwind'
, 'd:\Data\Northwind\'
, 'sa'
, ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
SELECT -- 'EXEC Master..xp_cmdshell ' +
-- '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
+ 'out ' + @.fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat '
+ '-S'+@.@.SERVERNAME+' -U'+@.User+' -P'+@.Pwd+' '
+ '-f'+@.fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt '
+ ' > ' + @.fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
-- + ', no_output' AS CMD
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @.CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @.CMD

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.CMD
SELECT @.CMD = 'ECHO ' + @.CMD + ' > ' + @.fp + '\bcpout.bat'
EXEC master..xp_cmdshell @.CMD
SELECT @.CMD = @.fp + '\bcpout.bat'
SELECT @.CMD
insert a (s)
exec master..xp_cmdshell @.cmd

FETCH NEXT FROM bcpout INTO @.CMD
END

CLOSE bcpout
DEALLOCATE bcpout

select id, ouputtmp = s from a

SET NOCOUNT OFF

drop table emp2|||The general sequence is this:

1) Research and find all columns that are defined as char, varchar, text or ntext.
2) Find all indexes that include any of these columns
3) Drop all indexes found in step 2
4) Issue the "alter table alter column" command to change the collation of each char column.
5) drop the index you missed in step 3 (always happens to me, anyway)
6) Finish altering tables
7) recreate all indexes that have been dropped.

Before you go through this, you may want to get a script of all of the indexes, in case you miss one here or there. A backup of the whole database is a handy thing, as well. Good luck.