Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    8

    Question Unanswered: How to search for and replace a value in all tables using a sql script.

    Hello Everyone,

    Is there a way to search all the tables in a database for a value that is found in a specific column? Another problem is that although this specific column will be found in most of the tables in the database, it has a different two or three digit prefix in the column name for each table.

    I think the logic will go something like this...

    As the script jumps from table to table, it should first look for a column whose name ends with ***ITM. If it does not find a column with ***ITM it should move on to the next table. If it does, then search the ***ITM column for my value. If it does not find my value, then move to the next table. But if it does, change my value to a new value.

    Any help will be greatly appreciated.

    Thank you all...
    Last edited by Jovix; 03-15-07 at 15:08.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    if u r in SQL 2K, this script will generate all the update SQLs needed. though by using exec in the script u can directly execute the updates as well, but it is better to check them before execution

    Code:
    exec sp_msforeachtable 'declare @Fld varchar(100) select top 1 @Fld=Column_Name from information_schema.Columns where Table_Name = parsename(''?'',1) and Column_Name like ''%Sal'' if @Fld is not null print (''update ? set '' + @Fld + ''=New_Value where '' + @Fld + ''=My_value '') '

  3. #3
    Join Date
    Aug 2004
    Posts
    8
    Thanks so much, like you mentioned I would like to see the items that need to be updated before executing the actual update. Can you help me revise the scipt so that it becomes a select query retrieving only the items that need to be updated.

    Thanks again.

  4. #4
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    here it is

    Code:
    exec sp_msforeachtable 'declare @Fld varchar(100) select top 1 @Fld=Column_Name from information_schema.Columns where Table_Name = parsename(''?'',1) and Column_Name like ''%ITM'' if @Fld is not null print (''select '' + @Fld + '',* from ? where '' + @Fld + ''=My_value '') '

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •