Add similar fields to serval tables

• thebeginner

  SSC Journeyman

  Points: 81

  More actions

  December 15, 2004 at 6:42 am

  #84387

  Folks, i have to create four fields in every user table within my database:

  Quote:

  CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]

  There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!

  Quote:

  declare @name VARCHAR (50) declare cur cursor fast_forward for select name from sysobjects where type='u' and status not like '-%' open cur WHILE (1=1) BEGIN FETCH NEXT FROM cur INTO @name IF @@fetch_status = 0 BEGIN ALTER TABLE @name ADD created_by [VARCHAR] (25) GO ALTER TABLE @name ADD created_by [VARCHAR] (25) GO ALTER TABLE @name ADD created_date [DATETIME] GO ALTER TABLE @name ADD modified_by [VARCHAR] (25) GO ALTER TABLE @name ADD modified_date [DATETIME] END ELSE BREAK END DEALLOCATE cur

  I also want that if one column for a table exists; the other columns should be created rather than it quits.

  Howdy!

  __________________________________
  A DBA works only when the users can't, so i scarcely work!

• Ian Yates

  SSCoach

  Points: 19738

  More actions

  December 15, 2004 at 8:01 am

  #533955

  You are near the mark, but you will need to employ dynamic SQL.
  
  So the part of your code that looks like
  ALTER TABLE @name
  ADD created_by [VARCHAR] (25)
  GO
  ALTER TABLE @name
  ADD created_by [VARCHAR] (25)
  GO
  ALTER TABLE @name
  ADD created_date [DATETIME]
  GO
  ALTER TABLE @name
  ADD modified_by [VARCHAR] (25)
  GO
  ALTER TABLE @name
  ADD modified_date [DATETIME]
  
  

  Should instead look something like
  declare @sql varchar(8000)
  set @sql = '
  ALTER TABLE @name
  ADD created_by [VARCHAR] (25)
  ALTER TABLE @name
  ADD created_by [VARCHAR] (25)
  ALTER TABLE @name
  ADD created_date [DATETIME]
  ALTER TABLE @name
  ADD modified_by [VARCHAR] (25)
  ALTER TABLE @name
  ADD modified_date [DATETIME]
  '
  
  

  and then have an 
  exec(@sql)
  
  which will execute the sql string.