Can I update many tables based on another table containing the table names on a column?

Question

Current working on a project where I have to update the data on 85 tables replacing the current empty string to a NULL value. it is a simple SQL query for this but since it is a sensitive environment, if anything goes wrong, we need to revert this.

The main idea was to create a table to save the data to roll back. but I am trying to avoid creating 85 tables.

I will give a smaller example:

there is 4 tables

   ------------------------------------
   |            airplane              |
   ------------------------------------
   | air_ID | color | tail_number     |
   ------------------------------------
   |  1     | red   |                 |
   |  2     | green |                 |
   |  3     | black |  21AF           |
   ------------------------------------
   ------------------------------------
   |            bus                   |
   ------------------------------------
   | bus_ID | color | tag_number      |
   ------------------------------------
   |  1     | red   |  AAY-464        |
   |  2     | green |                 |
   |  3     | black |                 |
   ------------------------------------
   ------------------------------------
   |            train                 |
   ------------------------------------
   | tr_ID  | color | designated_name |
   ------------------------------------
   |  1     | red   |  99212          |
   |  2     | green |                 |
   |  3     | black |                 |
   ------------------------------------
   ------------------------------------
   |         Cruise_Ship              |
   ------------------------------------
   | sea_ID | color | hull_number     |
   ------------------------------------
   |  1     | red   |                 |
   |  2     | green |  MAGDA          |
   |  3     | black |                 |
   ------------------------------------

So I created a temp table with the data

    -------------------------------------------------
    |         update_table                          |
    -------------------------------------------------
    | table_name | ID_colname | ID  |  col_name     |
    -------------------------------------------------
    |   airplane |   air_ID   |  1  | tail_number   |
    |   airplane |   air_ID   |  2  | tail_number   |
    |    bus     |   bus_ID   | 2   | tag_number    |
    |    bus     |   bus_ID   | 3   | tag_number    |
    |    train   |   tr_ID    |  2  |designated_name|
    |    train   |   tr_ID    |  3  |designated_name|
    |Cruise_Ship |   sea_ID   |  1  |  hull_number  |
    |Cruise_Ship |   sea_ID   |  3  |  hull_number  |
    -------------------------------------------------

With this table I was trying to generate a dynamic SQL to update all the tables with one call

    SET @SQLString = N'UPDATE @table 
        SET  @value = '+ @empty +'
        where @key = @id';

    SET @ParmDefinition = N'@table nvarchar(max),
        @value nvarchar(max) ,
        @key nvarchar(max) ,
        @id int';

    DECLARE @table nvarchar(255)
    DECLARE @value nvarchar(255)
    DECLARE @key nvarchar(255)
    DECLARE @id int

    select @table = table_name, @id = ID, @key = ID_colname , @value = col_name from update_table

    EXECUTE sp_executesql
        @SQLString
        ,@ParmDefinition
        ,@table
        ,@value
        ,@key
        ,@id
        ;

But this is not working, anyone has a idea on how to improve this query?

This is a high profile environment and the developers are not the ones executing the code, so it need to be customer proof. The code is ran overnight to not disturb daytime operations.

Answer 1

According to SQL Server documentation:

If a SELECT statement returns more than one row and the variable references a non-scalar expression, the variable is set to the value returned for the expression in the last row of the result set.

That means your variables were assigned with the values from the last row only. Therefore only [Cruise_Ship].[hull_number] will be pass to sp_executesql and that is the only column being updated with your script.

To store mutliple values, a table variable should be used.

sp_executesql does accept parameters and it is used for building dynamic queries.

However I don't think you can pass table-valued variable as parameters.

added: Check this for how to pass table-valued variable to sp_executesql.

This is where your code goes wrong.

select @table = table_name, @id = ID, @key = ID_colname , @value = col_name from update_table

I know this is not elegant but the following code should do the job. And I whould suggest to wrap it in a TRANSACTION whether you did a backup or not.

DECLARE @empty NVARCHAR(10) 
SET @empty = 'NULL'

SELECT
    'UPDATE ' + s.name + '.' + t.name + ' 
    SET [' + c.name + '] =  ' + @empty + ' 
    WHERE LTRIM([' + c.name + ']) = ''''
    END;'
FROM sys.tables t
INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
INNER JOIN sys.columns c
    ON t.object_id = c.object_id
ORDER BY
    s.name
   ,t.name
   ,c.column_id

Answer 2

I agree with Adam Yam, I just expanded the answer to include the needed data from the update_tables.

This way the execution of the SQL will only happen for those 8 entries on the table.

the result is the update to the 4 tables from the example correctly.

DECLARE @currentId INT 
SELECT @currentId = MIN(tabl.ID) from udpate_table tabl

DECLARE @sql NVARCHAR(MAX)

WHILE (1 = 1)
BEGIN  
   --- execute for the current pk
    BEGIN
        SELECT @sql =
        'UPDATE ' + s.name + '.' + t.name + ' 
        SET [' + c.name + '] = '''' 
           where  ' + s.name + '.' + t.name + '.' + tab.ID_colname + ' = ' + convert (varchar(20), tab.ID) + ' '
        FROM sys.tables t
        INNER JOIN sys.schemas s
            ON t.schema_id = s.schema_id
        INNER JOIN sys.columns c
            ON t.object_id = c.object_id
        JOIN update_table tab
            on t.name = tab.table_name
            and c.name = tab.col_name
            and tab.ID = @currentId
        ORDER BY
            tab.ID
           ,s.name
           ,t.name
           ,c.column_id
    END

    exec sp_executesql @sql

    -- select the next id to handle    
  SELECT TOP 1 @currentId = tabl.ID
  FROM update_table tabl
  WHERE tabl.ID > @currentId 
  ORDER BY tabl.ID

  IF @@ROWCOUNT = 0 BREAK;

END