MS SQL Server - Increase column length in all tables with column

Author

Increase column length in all tables with column

Alan Z. Scharf

2006-03-23, 1:23 pm

Hi,

I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?

Thanks.

Alan

SQLpro [MVP]

2006-03-23, 1:23 pm

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
>
>

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************
* http://www.datasapiens.com ********************
***

Alan Z. Scharf

2006-03-23, 1:23 pm

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[color=darkred]
>
> 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 +
>
>
>
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************
* http://www.datasapiens.com ********************
***

Doug

2006-03-25, 1:23 pm

look up the table "syscolumns" in books on line.

Sponsored Links