3

I have a massive database that I don't mind leaving to search for a while, but I can't dump the whole database for various reasons. What is the easiest query I can write that will search all fields of all tables in a database for a specific string of text?

The following won't work but should demonstrate what I would like to see:

 SELECT * FROM * where * like '%mystring%'

Any ideas?

rockstardev
  • 13,479
  • 39
  • 164
  • 296
  • Why do you want to do this? I can't think of a scenario. – user247702 Jun 14 '13 at 13:28
  • 2
    I usually do that by searching the dump (with `grep` or `vim`). Chances are, if you are not allowed to dump the database, you are also not allowed to read all tables ... – Erwin Brandstetter Jun 14 '13 at 13:49
  • 2
    In theory you could do this using PL/PgSQL's `EXECUTE`, the `format` statement and some complex queries against `pg_catalog.pg_class`, `pg_catalog.pg_attribute`, etc. It's going to be a major pain to do, slow, and generally awful; I can't imagine why you'd want to do this. – Craig Ringer Jun 14 '13 at 13:50

2 Answers2

10

You could take advantage of the fact that in PostgreSQL, tablename.* can be cast to text.

So, SELECT t.* FROM tablename t WHERE (t.*)::text LIKE '%somestring%' will return any row where any column contains somestring, no matter what the type of the column.

If used in a loop on SELECT table_schema, table_name FROM information_schema, it's comparable to a grep within a database dump, except you don't need the dump.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
5

A SELECT statement always returns a result-set. A result set is a kind of table with column names and one row for each result.

You are looking for a string. So there is no need to query any non-textual column at all.

The 'from'-clause of the select has to be specified. You could query the metadata information of your database about all table names. Then obtain all columns which are textual (like char, varchar, clob, ...) to be used in the 'where'-clause. Then apply the constructed select on each table.

This algorithm could be expressed in a stored procedure or by any program.

What prevents you from dumping the database? On a UNIX system you could pipe the dump directly to the 'grep' command.

Jemolah
  • 1,962
  • 3
  • 24
  • 41