A long time ago a database developer asked me how she could produce a list of all table columns in the database which store a given value.
Note that she was in the process of normalizing tables and wanted to find every fact table that referenced a particular string value to replace it with a dimension table look up.
The simple solution I provided her in that database (i.e. Oracle) is easily modified so that it works in YugabyteDB YSQL!
Example:
Say I have these four tables:
yugabyte=# SELECT * FROM test.table1;
name
------
Jane
(1 row)
yugabyte=# SELECT * FROM test.table2;
name
------
Jane
(1 row)
yugabyte=# SELECT * FROM test.table3;
user_name
-----------
Jane
(1 row)
yugabyte=# SELECT * FROM test.table4;
first_name
------------
Jim
(1 row)
To find which columns contain the name “Jane” I could run this:
yugabyte=# \! ysqlsh -U yugabyte -h 10.37.1.249 -Atc "SELECT 'SELECT ''' || table_schema || '.' || table_name || '.' || column_name || ''' WHERE EXISTS (SELECT NULL FROM ' || table_schema || '.' || table_name || ' WHERE ' || column_name || '::VARCHAR = ''Jane'');' FROM information_schema.columns WHERE table_schema = 'test' ORDER BY table_schema, table_name, ordinal_position;" | ysqlsh -U yugabyte -h 10.37.1.249 -At
test.table1.name
test.table2.name
test.table3.user_name
And to find which columns contain the name “Jim” I could run this:
yugabyte=# \! ysqlsh -U yugabyte -h 10.37.1.249 -Atc "SELECT 'SELECT ''' || table_schema || '.' || table_name || '.' || column_name || ''' WHERE EXISTS (SELECT NULL FROM ' || table_schema || '.' || table_name || ' WHERE ' || column_name || '::VARCHAR = ''Jim'');' FROM information_schema.columns WHERE table_schema = 'test' ORDER BY table_schema, table_name, ordinal_position;" | ysqlsh -U yugabyte -h 10.37.1.249 -At
test.table4.first_name
Have Fun!
