Increase column length in all tables with column

Hi,
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
Alan
Alan Z. Scharf a crit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +

> Thanks.
> Alan
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Alan Z. Scharf a crit :
in
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
|||look up the table "syscolumns" in books on line.

Increase column length in all tables with column

Hi,
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
AlanAlan Z. Scharf a crit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +

> Thanks.
> Alan
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...
> Alan Z. Scharf a crit :
in[vbcol=seagreen]
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||look up the table "syscolumns" in books on line.