Alter multiple tables

• aparnasantoshnair

  SSC Rookie

  Points: 28

  More actions

  October 21, 2010 at 10:33 am

  #240006

  Hi,

  Is there anyway to alter multiple tables within the same database like generating scripts to create multiple tables.

  Thanks & Regards

• Lowell

  SSC Guru

  Points: 323546

  More actions

  October 21, 2010 at 11:10 am

  #1240473

  yes.

  each ALTER TABLE statement must be executed seperately, but you can generate all the statements based on the metadata, so it depends what you want to do.

  an example might be if you were going to add a "CreatedDt" column to every table that happens to have a name that starts with "TB":

  SELECT

  /*--results

  ALTER TABLE [TBACCT7] ADD CREATEDDT datetime DEFAULT getdate()

  ALTER TABLE [TBFEEINC] ADD CREATEDDT datetime DEFAULT getdate()

  ALTER TABLE [TBCOMPLSPEC] ADD CREATEDDT datetime DEFAULT getdate()

  */

  SELECT

  'ALTER TABLE [' + name + '] ADD CREATEDDT datetime DEFAULT getdate();'

  FROM

  sys.tables

  WHERE name like 'TB%'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738683

  More actions

  October 21, 2010 at 11:22 am

  #1240480

  Note that you would take the output of Lowell's script and execute it in SSMS as a query to alter all tables.

  The idea is that you are using code to generate other code.

  http://www.sqlservercentral.com/articles/Advanced+Querying/codegenerationusingsqltogeneratesqlandothercode/1717/