June 9, 2004 at 10:12 am
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 ,
[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
June 10, 2004 at 1:18 am
Horrible kludge. Script all the tables out to a text file in Enterprise Manager. Load text file into a suitable editor e.g. notepad. Use find and replace to change CREATE to ALTER,use find and replace to change COLLATE Greek_CI_AS to something suitable or simply replace it with nothing. Save the file, which should have a .sql extension.
Load query analyser, go to your database, load the .SQL file from above. Check the syntax, ( just to be on the safe side), execute it.
Should be done.
Hope this helps.
June 10, 2004 at 1:26 am
Two ways comes to mind.
Either bcp out all your data to textfiles (not native), drop tables and recreate them and bcp data back in, or....
Alter table alter column... for each column.
-- quote BOL on ALTER TABLE subject --
COLLATE
Specifies the new collation for the altered column. Collation name can be either a Windows collation name or a SQL collation name. For a list and more information, see Windows Collation Name and SQL Collation Name.
The COLLATE clause can be used to alter the collations only of columns of the char, varchar, text, nchar, nvarchar, and ntext data types. If not specified, the column is assigned the default collation of the database.
ALTER COLUMN cannot have a collation change if any of the following conditions apply:
If a check constraint, foreign key constraint, or computed columns reference the column changed.
If any index, statistics, or full-text index are created on the column. Statistics created automatically on the column changed will be dropped if the column collation is altered.
If a SCHEMABOUND view or function references the column.
For more information about the COLLATE clause, see COLLATE.
-- end BOL --
/Kenneth
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply