Alter table alter column syntax 
Author Message
 Alter table alter column syntax
I'm trying to write a script to alter 3 columns at the same time, such as:

alter table Table1 alter column
    col1 varchar(30) null,
    col2 varchar(30) null,
    col3 varchar(30) null
go

I've read the BOL and I've tried this and quite a few other combinations of
brackets and commas and still cannot get it right.  I keep getting syntax
errors.

Is it even possible to modify multiple columns at once?



Wed, 02 Mar 2005 02:35:55 GMT
 Alter table alter column syntax

Jim,

Quote:
>> Is it even possible to modify multiple columns at once?

No....either you have to do seperate ALTER TABLE table1 ALTER COLUMN ...
commands OR the workaround would be to use sql to generate script for you,
as in,

SELECT 'ALTER TABLE table1 ALTER COLUMN '+column_name+' VARCHAR(30)
NULL'+CHAR(13)+'GO'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table1'

The above code would generate ALTER TABLE table1 ALTER COLUMN ...commands
for all columns of table1.You can filter it further based upon other columns
of INFORMATION_SCHEMA.COLUMNS like column_name,data_type etc, as in

SELECT 'ALTER TABLE table1 ALTER COLUMN '+column_name+' VARCHAR(30)
NULL'+CHAR(13)+'GO'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table1'
AND column_name IN('col1','col2','col3')

Once the code is executed, it generates a sql script.Copy the sql script
from results pane and execute it .

Dinesh
--
--
SQL Server FAQ at
www.tkdinesh.com


Quote:
> I'm trying to write a script to alter 3 columns at the same time, such as:

> alter table Table1 alter column
>     col1 varchar(30) null,
>     col2 varchar(30) null,
>     col3 varchar(30) null
> go

> I've read the BOL and I've tried this and quite a few other combinations
of
> brackets and commas and still cannot get it right.  I keep getting syntax
> errors.

> Is it even possible to modify multiple columns at once?



Wed, 02 Mar 2005 02:53:57 GMT
 Alter table alter column syntax
OK, thanks


Quote:
> Jim,

> >> Is it even possible to modify multiple columns at once?

> No....either you have to do seperate ALTER TABLE table1 ALTER COLUMN ...
> commands OR the workaround would be to use sql to generate script for you,
> as in,

> SELECT 'ALTER TABLE table1 ALTER COLUMN '+column_name+' VARCHAR(30)
> NULL'+CHAR(13)+'GO'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE table_name = 'table1'

> The above code would generate ALTER TABLE table1 ALTER COLUMN ...commands
> for all columns of table1.You can filter it further based upon other
columns
> of INFORMATION_SCHEMA.COLUMNS like column_name,data_type etc, as in

> SELECT 'ALTER TABLE table1 ALTER COLUMN '+column_name+' VARCHAR(30)
> NULL'+CHAR(13)+'GO'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE table_name = 'table1'
> AND column_name IN('col1','col2','col3')

> Once the code is executed, it generates a sql script.Copy the sql script
> from results pane and execute it .

> Dinesh
> --
> --
> SQL Server FAQ at
> www.tkdinesh.com



> > I'm trying to write a script to alter 3 columns at the same time, such
as:

> > alter table Table1 alter column
> >     col1 varchar(30) null,
> >     col2 varchar(30) null,
> >     col3 varchar(30) null
> > go

> > I've read the BOL and I've tried this and quite a few other combinations
> of
> > brackets and commas and still cannot get it right.  I keep getting
syntax
> > errors.

> > Is it even possible to modify multiple columns at once?



Wed, 02 Mar 2005 04:11:18 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Alter Table Alter Column Syntax

2. syntax for ALTER TABLE ALTER COLUMN DEFAULT

3. Syntax for using ALTER...ALTER COLUMN...

4. multiple ALTER COLUMNs in one ALTER TABLE statement?

5. Help with Alter Table Alter Column

6. Alter table alter column

7. alter table alter column

8. Alter table Alter column problem.

9. ALTER TABLE ... ALTER COLUMN IDENTITY

10. ALTER TABLE ALTER COLUMN

11. ALTER TABLE ALTER COLUMN to set Default

12. Alter table alter column text


 
Powered by phpBB® Forum Software