Enterprise manager table design

I have table with more then 100 coluns . I must change default value
from 0 to 1 in 90 columns. Is there a way to do this in "table design"
in "all in once" metod.Hi
I prefer to write a script does the job .Don't forget to drop first
constraints and then re-create with DEFAULT (1)
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1131349106.061862.27390@.z14g2000cwz.googlegroups.com...
>I have table with more then 100 coluns . I must change default value
> from 0 to 1 in 90 columns. Is there a way to do this in "table design"
> in "all in once" metod.
>|||Hi
To add to Uri's suggestion, you can script the table by right clicking the
table in EM and choose the Generate SQL option. Then when you have the file
it can be edited (possibly in Query Analyser!) where global replacement coul
d
save you time. You can then run the script in Query Analyser or from a
command prompt using OSQL.
If you don't want to loose the data from the table then you may want to copy
the contents into a temporary table and then load them back if you are using
the drop/create method.
John
"nywebmaster" wrote:

> I have table with more then 100 coluns . I must change default value
> from 0 to 1 in 90 columns. Is there a way to do this in "table design"
> in "all in once" metod.
>|||On 6 Nov 2005 23:38:26 -0800, nywebmaster wrote:

>I have table with more then 100 coluns . I must change default value
>from 0 to 1 in 90 columns. Is there a way to do this in "table design"
>in "all in once" metod.
Hi nywebmaster,
This question suggests that you have a repeating group in your table.
That is a sign of a weak design. I'll give you an example:
Weak design:
CREATE TABLE SalesPersons
(SalesPersonID int NOT NULL
SalesPersonName varchar(50) NOT NULL,
SalesJan decimal(9,2) DEFAULT NULL,
SalesFeb decimal(9,2) DEFAULT NULL,
SalesMar decimal(9,2) DEFAULT NULL,
SalesApr decimal(9,2) DEFAULT NULL,
SalesMay decimal(9,2) DEFAULT NULL,
SalesJun decimal(9,2) DEFAULT NULL,
SalesJul decimal(9,2) DEFAULT NULL,
SalesAug decimal(9,2) DEFAULT NULL,
SalesSep decimal(9,2) DEFAULT NULL,
SalesOct decimal(9,2) DEFAULT NULL,
SalesNov decimal(9,2) DEFAULT NULL,
SalesDec decimal(9,2) DEFAULT NULL,
PRIMARY KEY (SalesPersonID)
)
Better design:
CREATE TABLE SalesPersons
(SalesPersonID int NOT NULL
SalesPersonName varchar(50) NOT NULL,
PRIMARY KEY (SalesPersonID)
)
CREATE TABLE Sales
(SalesPersonID int NOT NULL,
MonthNo tinyint NOT NULL,
Sales decimal(9,2) NOT NULL,
PRIMARY KEY (SalesPersonID, MonthNo)
FOREIGN KEY (SalesPersonID)
REFERENCES SalesPersons (SalesPersonID)
CHECK (MonthNo BETWEEN 1 AND 12)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)