-2

I would like to update all columns with column name 'USER_KEY' to value 'admin'. Is this possible to do?

I can get all table and column names with a specified column name with the following sql script: 

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%USER_KEY%'
ORDER BY    TableName
            ,ColumnName;

But I don't know how to use these in an update statement.

blep
  • 7
  • 2
  • Is this a one-time task or do you need to do this repeatedly? – Filburt May 28 '18 at 08:33
  • It's a one-time task. – blep May 28 '18 at 10:02
  • 1
    If it's a one-time task, you could simply spit out the UPDATE statement for every single result row (concatenate the statement around `c.name` and `t.name`) to a text result window, copy-n-past the generated script and run it. – Filburt May 28 '18 at 11:51

1 Answers1

0

You can achieve this via PL/SQL (note this is oracle syntax but the idea should be the same for SQL server other SQL). something like the following, build your list/table details, pass the values by building on the sql update statements and execute the statement all within the loop...

SET AUTOCOMMIT ON
BEGIN
FOR my_table_colums in (
select      c.name  as 'columnname'
            ,t.name as 'tablename'
from        sys.columns c
join        sys.tables  t   on c.object_id = t.object_id
where       c.name like '%user_key%'
order by    tablename
            ,columnname)
LOOP
/*-- build your dynamic sql and assign to a variable --*/
mysql_update_statement := 'update '||table_colums.tablename ||' set  '||table_colums.columnname||'= '''admin''';

/*-- display your dynamic update sql--*/
DBMS_OUTPUT.PUT_LINE(mysql_update_statement );

/*-- execute your dynamic update sql--*/
EXECUTE IMMEDIATE mysql_update_statement;
END LOOP;
END;
/
rb1234
  • 37
  • 2
  • Thanks. I will need to rewrite it to see if it works because Mssql does not have for loops and some other commands. – blep May 28 '18 at 10:03