Always grateful for the infinite wisdom from everyone on this site.
I deal with some massive databases that are often difficult to search in. One of which is a lead database with over 1.4k columns and 3 million records.
Sometimes, I just want to query to see if a certain value exists anywhere in the database within ANY field. (Like a ctrl+F in Excel) Is it possible to perform a query that searches for a particular string or integer in all records and within any field in the database? I assume this would take a lot of computing power but it would save me a lot of time instead of trying to search for the correct field I need to be using as there are multiple fields with similar names and no concrete ER Diagram
Advertisement
Answer
Below example shows how to do so
with your_table as ( select 1 x, 'abc' y, 'table1' z union all select 2, 'xyz', 'desk3' )select format('%t', t) record, contains_substr(t, 'xyz') has_xyz, contains_substr(t, '1') has_1, contains_substr(t, '3') has_3,from your_table t with output
