Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Question Unanswered: 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.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: How to change COLLATION NAME for the database?

    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.

  3. #3
    Join Date
    Nov 2003
    Posts
    94
    If your database has a reasonably conventional design you are welcome to try my script generator

    http://www.dbforums.com/showthread.p...ight=collation

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •