Artful Common Queries

Generate a query from information_schema

from the Artful Common Queries page

SQL is famously literal, but information_schema offers many opportunities for query generation. For example, here's a straightforward information_schema query to generate SQL statements that will replace 'old_text' with 'new_text' in all string columns in database dbname ...

Select Concat( 'update ', table_schema, '.', table_name, 
               ' set ', column_name, 
               '=replace(', column_name, ',''old_text'',''new_text'');'
             )
From information_schema.columns
Where (data_type Like '%char%' or data_type like '%text' or data_type like '%binary')
  And table_schema = 'dbname';

Last updated 15 Mar 2025