Alter Tables

[myNameIsRon]

Hi, I'm wondering if it's possible to join ALTER TABLE statements so I don't have to have a bunch of ExecuteNonQuery commands. ALTER TABLE Products ALTER COLUMN ItemNumber TEXT(15) ALTER TABLE ItemsSold ALTER COLUMN ItemNumber TEXT(15) thanks, Ron

[r stropek]

As far as I know this is not possible. Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

[Eric Dahlvang]

You could create this stored procedure, then just execute it with a delimited list of table names:

CREATE PROCEDURE AlterMyTables
@cTablesToAlter varchar(500)
AS

declare @cSQL varchar(100)
declare @nLoc int
declare @nLoc2 int

select @nLoc = 1

while (@nLoc<len(@cTablesToAlter))
begin
select @nLoc2 = CHARINDEX(',',@cTablesToAlter,@nLoc + 1 )

if (@nLoc2=0)
  	select @nLoc2 = len(@cTablesToAlter) +1

select @cSQL = 'ALTER TABLE ' + SUBSTRING(@cTablesToAlter,@nLoc,@nLoc2-@nLoc) + ' ALTER COLUMN ItemNumber TEXT'
exec(@cSQL)

select @nLoc = @nLoc2 +1

end

---------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters

[myNameIsRon]

Thanks Eric, I'll give it a try. Ron

[myNameIsRon]

Thanks Rainer. Ron