Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    10

    Unanswered: Change collations in many tables defined using COLLATE

    I have (inherited a) a db with lots of tables looking like the definition at the end of the message.

    I need to change all these tables, so that all the textual fields follow the default DB collation.

    Any help appreciated!
    THANX...

    CREATE TABLE [Club] (
    [id] [int] NOT NULL ,
    [name] [varchar] (100) COLLATE Greek_CI_AS NOT NULL ,
    [address] [varchar] (255) COLLATE Greek_CI_AS NULL ,
    [zoomAreaId] [int] NULL ,
    [phone] [varchar] (255) COLLATE Greek_CI_AS NULL ,
    [contact] [varchar] (50) COLLATE Greek_CI_AS NULL ,
    [clubCategoryId] [int] NULL ,
    [unused] [varchar] (2) COLLATE Greek_CI_AS NULL ,
    [monday] [int] NULL ,
    [tuesday] [int] NULL ,
    [wednesday] [int] NULL ,
    [thursday] [int] NULL ,
    [friday] [int] NULL ,
    [saturday] [int] NULL ,
    [sunday] [int] NULL ,
    [comments] [text] COLLATE Greek_CI_AS NULL ,
    [URL] [varchar] (255) COLLATE Greek_CI_AS NULL ,
    [image] [varchar] (50) COLLATE Greek_CI_AS NULL ,
    [priceCategoryId] [int] NULL ,
    [ratingId] [int] NULL ,
    [entryDate] [datetime] NULL ,
    [WAPText] [varchar] (255) COLLATE Greek_CI_AS NULL ,
    [SMSText] [varchar] (160) COLLATE Greek_CI_AS NULL ,
    [SMSAddress] [varchar] (50) COLLATE Greek_CI_AS NULL ,
    [active] [bit] NOT NULL ,
    [content_id] [bigint] NULL ,
    [disp_as_propos] [bit] NULL ,
    [date2disp] [datetime] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ALTER TABLE...ALTER COLUMN would do the trick, but you have to drop all indexes and all constraints. If you have DBArtisan it would be very easy to do, or you can script both DROP <table_name>.<index_name> and ALTER TABLE <table_name> DROP CONSTRAINT and then re-create them yourself.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Also, BOL:

    You cannot alter the collation of a column that is currently referenced by:

    A computed column.
    An index.
    Distribution statistics, either generated automatically or by the CREATE STATISTICS statement.
    A CHECK constraint.
    A FOREIGN KEY constraint.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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