Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to update multiple column definition in single update statement Expand / Collapse
Author
Message
Posted Thursday, December 30, 2010 7:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 02, 2011 2:56 PM
Points: 23, Visits: 117
Hi All,
Does anybody know if there is a way to update column definition of multiple columns in a single ALTER statement in SQL Server 2008 or Denali CTP 1 ?
I am aware it is not possible in SQL Server 2005.

Thank You
Post #1040888
Posted Thursday, December 30, 2010 7:59 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195
Can you elaborate in better
can you have some examples for it like how with scenarios

Thanks
Parthi


Thanks
Parthi
Post #1040911
Posted Thursday, December 30, 2010 8:21 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
I don't think you can do it in SQL 2008 R2 either. Not sure about Denali.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1040926
Posted Thursday, December 30, 2010 8:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 02, 2011 2:56 PM
Points: 23, Visits: 117
I need to alter definition of two columns in a table and its being done like this :

ALTER TABLE dbo.JURISDICTION_FIELD
ALTER COLUMN FIELD_CODE NVARCHAR(60) NOT NULL;

ALTER TABLE dbo.JURISDICTION_FIELD
ALTER COLUMN [DESCRIPTION] NVARCHAR(2000) NULL;

If I need to update 20 columns in a table, there will need to be 20 ALTER TABLE statements. Is there a way to update all 20 columns using a single ALTER TABLE statement?

Please let me know if you need more clarifications.

Thank You.
Post #1040935
Posted Thursday, December 30, 2010 8:33 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
I don't believe there is a way to do that. You can add multiple columns in a single command, but not alter multiple columns all at once.

Copy-and-paste can remove most of the work with that. Might be even simpler if you're modifying a predictable set of columns, and you query sys.columns to build the basic strings.

select 'alter table [' + object_name(object_id) + '] alter column [' + name + ']' 
from sys.columns
where object_id = ...;

Something like that.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1040938
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse