How to change COLLATION NAME for the database?

Is there a way (besides "ALTER DATABASE COLLATE ...") to change collation name for the whole database? I tried to use the "ALTER DATABASE" command, but it didn't work. And I wouldn't like to run "ALTER COLUMN" commands for over 100 tables.Originally posted by zuhara
Is there a way (besides "ALTER DATABASE COLLATE ...") to change collation name for the whole database? I tried to use the "ALTER DATABASE" command, but it didn't work. And I wouldn't like to run "ALTER COLUMN" commands for over 100 tables.

Altering the default collation of a database does not change the collations of the columns in any existing user-defined tables. These can be changed with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:

The default collation for the database. This new default collation is applied to all columns, user-defined data types, variables, and parameters subsequently created in the database. It is also used when resolving the object identifiers specified in SQL statements against the objects defined in the database.

Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables to the new collation.

All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions to the new collation.

The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, to the new default collation.
After a collation has been assigned to any object other than a column or database, you cannot change the collation except by dropping and re-creating the object. This can be a complex operation. To change the default collation for an instance of Microsoft SQL Server 2000 you must:

Make sure you have all of the information or scripts needed to re-create your user databases and all of the objects in them.

Export all of your data using a tool such as bulk copy.

Drop all of the user databases.

Rebuild the master database specifying the new collation.

Create all of the databases and all of the objects in them.

Import all of your data.|||If your database has a reasonably conventional design you are welcome to try my script generator

http://www.dbforums.com/showthread.php?threadid=926370&highlight=collation

which will generate a t-sql script for manually tearing down and rebuilding all collatable columns in a dabase.