Ggtcf

I working on a SQL query to update a list of tables and columns in a database. Any suggestions are welcome.

I am using information_schema.tables and information_schema.columns, lookup table, and a table variable. However, I am having an issue updating.

I started with the join to ensure I am matching the right table and column needed to the lookup table.

I need to loop through and update tables where matched to the objectname if table.column.

i was trying to use a cursor but did some research and thought table variable may work better for optimization and performance.
I declared my variables and create a table variable but it is still not updating. I started with the below query after I created and populated my table variable.

I tried different ways with a cursor and table variable but I am not getting it right to complete the update.

'Select l.objectname, l.fieldname, l.newpvalue, l.oldpvalue from lookup_table 1 inner join information_schema.tables t
 on l.objectname = t.table_name
 inner join information_schema.columns c
 on l.fieldname = c.column_name and l.objectname 
 = c.table_name'

Here is the new update that I am attempting to parameterized

SELECT 'UPDATE' + I.TABLE_NAME + 'SET C.COLUMN_NAME = 
 CASE 
 WHEN TP.[listValue] = TP.[OldValue]
 WHEN TP.[listValue] != TP.[OldValue] 
 THEN SET TP.NOTES = C.COLUMN_NAME '-' TP.[OldValue]
 AND SET C.COLUMN_NAME = NULL
 END
 FROM 
 INFORMATION_SCHEMA.TABLES I INNER JOIN [dbo].[tpRef] tp
 ON I.TABLE_NAME = tp.ObjectName
 INNER JOIN INFORMATION_SCHEMA.COLUMNS C
 ON tp.[FieldName] = C.COLUMN_NAME
 AND tp.[ObjectName] = C.TABLE_NAME
 LEFT JOIN INFORMATION_SCHEMA.COLUMNS C2
 ON tp.[FieldName] = C2.COLUMN_NAME
 AND C2.COLUMN_NAME = 'NOTES'
 WHERE TABLE_TYPE = 'base table'
 AND tp.ObjectName = 'table name'
 AND tp.FieldName = 'table field'
 AND tp.[listValue] = 'List' 

not a working cursor, needs the query to fill out the needed columns to populate the variables.

declare 
 @table_name varchar(128) 
 ,@schema_name varchar(128) -- dbo is the default schema, if custom make sure query returns the correct schema
 ,@update_column varchar(128) 
 ,@update_data varchar(1024)
 ,@where_column varchar(128)
 ,@where_data varchar(1024)
 ,@s varchar(max);
declare x cursor local fast_forward 
for
/*
 insert your query returning desired values to be looped through.
 */
select 1,2,3,4,5,6 --6 variables to populate. delete this line when you get your query written above :)
open x;
fetch next from x into @table_name,@update_column,@schema_name,@update_data,@where_column,@where_data;

while @@fetch_status = 0
 begin
 set @s = '
 update ' + quotename(@schema_name) + '.' + quotename(@table_name) + ' 
 set ' + quotename(@update_column) + ' = ''' + @update_data + ''' where ' + quotename(@where_column) + ' = ''' + @where_data + ''';
 '
 begin try
 --to execute it, you can use exec()
 exec(@s); 
 --to print out the string so you can read and or copy/paste to text execution use print()
 print(@s);
 /* 
 I would recommend print(@s); until you have all your bugs worked out.
 process the updates manually from the printed statements to verify syntax correctness and results
 */
 end try
 begin catch
 print error_message()
 end catch;
 -- this list of variables must match the first "fetch next" and all be declared and returned in the "for" query of the cursor.
 -- those that are not must be set inside the cursor by queries, calculations or hardcoded.
 fetch next from x into @table_name,@update_column,@schema_name,@update_data,@where_column,@where_data;
 end

close x;
deallocate x;

Cursor used is
Fast_forward(SO answer) = (read_only and forward_only)
Local(cursor performance breakdown) = local in scope, only valid within scope.

I still can't bring myself to use the information_schema views...

I was attempting to use table variable but are you indicating to use both a table variable and cursor?

When I run the below is takes a long time.

----UPDATE TABLE AND COLUMN FOUND

SELECT @STRUPDSQL =
 'UPDATE' + 'I.TABLE_NAME' + 'SET C.COLUMN_NAME = 
 CASE 
 WHEN TP.[PicklistValue] = TP.[OldValue]
 WHEN TP.[PicklistValue] != TP.[OldValue] 
 THEN SET TP.NOTES = C.COLUMN_NAME - TP.[OldValue]
 AND SET C.COLUMN_NAME = NULL
 END
 FROM 
 INFORMATION_SCHEMA.TABLES I INNER JOIN @TBLS T
 ON I.TABLE_NAME = T.v_TABLENAME

 WHERE T.v_TABLENAME =@l_TBLNM AND T.v_COLUMNNAME =@l_COLNNM 

'
 SET @I = @I + 1
 END

I have it working but the value that I want to update with is being read as a column name and not the column data value.

IF quotename(@COLNAME) IS NOT NULL

 BEGIN
 SET @SQL = 'UPDATE ' + quotename(@TABLENAME) + ' SET ' + quotename(@COLNAME) + ' = ' +'"' + @TVALUE + '"' + ' ' + 'WHERE ' + quotename(@COLNAME) + '= AccountSource'
 END