How to delete or Update data in Multiple table in DB


  • Dear All,

    In SQL Management Studio 2008 has one of the Database with 138 tables. May i know how to update or delete the data into all the tables ?

    example -  if update the ID in one short into all the tables 

    kindly advise, thank you. 

    Thursday, October 10, 2013 2:37 AM


  • Hi

    PFB code

    --method 1 
    EXEC sp_MSforeachtable 'UPDATE a set ID=2 from ? a where ID=3'
    --method 2
    Declare @output varchar(max)
    select @output =''
    SELECT @output += CHAR(13) + CHAR(10) 
      + 'Update a set a.'' =2 from ' + + ' a where ''=3'+' ;'
      FROM sys.tables AS t
      INNER JOIN sys.columns AS c
      ON t.[object_id] = c.[object_id]
      WHERE = 'ID';
    Print @output;
    -- EXEC sp_executesql @output;


    Saravana Kumar C

    Thursday, October 10, 2013 3:38 AM

All replies