Download all columns of all tables containing a text

Question

I would like to find out which are all columns of tables in a database in a way that it is possible to search a word in these columns.

Let’s say I have a database I don’t know the structure of but I want to search for a word in all the columns of the type character varying or blob (believe that only these store text) without need to look one by one and have to do the query manually.

This query would also work in a database that has different schemas?

Answer 1

In Postgres this information may be obtained from information_schema, of a VIEW calling for columns.

For example, consider the tables tb_foobar and tb_xpto:

CREATE TABLE public.tb_foobar
(
  id INTEGER,
  name CHARACTER VARYING(100),
  description TEXT,
  moment DATE,
  flag BOOLEAN,
  blah INTEGER,
  xpto NUMERIC(5,2)
);

CREATE TABLE public.tb_xpto
(
  id INTEGER,
  alpha CHARACTER VARYING(100),
  beta TEXT
);

Recovering all type columns CHARACTER VARYING and TEXT of all the tables in the schema public:

SELECT
  table_schema,
  table_name,
  column_name,
  data_type
FROM
  information_schema.columns
WHERE
  table_schema = 'public' AND
  data_type IN ('text','character varying')

Exit:

| table_schema | table_name | column_name |         data_type |
|--------------|------------|-------------|-------------------|
|       public |  tb_foobar |        name | character varying |
|       public |  tb_foobar | description |              text |
|       public |    tb_xpto |       alpha | character varying |
|       public |    tb_xpto |        beta |              text |

Sqlfiddle: http://sqlfiddle.com/#! 17/23840/2

Answer 2

I’ve done this job thinking of your need:

Basically selects all the columns of all the tables of the schema informed, and which are varchar or text.

Then go through mounting a query, and return the query result in a table.

Since all tables have different structures, I only returned the column in question, and the name of the table where the value was found:

CREATE OR REPLACE FUNCTION public.like_in_tables (valor varchar, sch varchar 
)
RETURNS table
(
coluna VARCHAR,
tabela varchar
)
 AS
$body$
    declare
       temprow record;
       cmd varchar;
       begin

       cmd := '';

        FOR temprow IN
        (
        select
x.table_name,
x.column_name
from information_schema.columns x
where x.table_schema = $2
and x.data_type in  ('character varying','text')
and x.is_updatable = 'YES'
         )
    LOOP
        cmd := cmd ||  ' SELECT '|| temprow.column_name ||'::varchar as coluna, '|| ''''|| temprow.table_name|| '''' ||'::varchar as tabela ' || ' FROM '|| temprow.table_name || ' where ' || temprow.column_name || '::varchar LIKE ' || '''' || $1 ||'''' || ' UNION '; 
    END LOOP;
        cmd := cmd || ' SELECT NULL, null ';

   RETURN QUERY EXECUTE cmd;   
       end;
$body$
LANGUAGE 'plpgsql'
CALLED ON NULL INPUT;

Using:

select * from like_in_tables('%LOPES%','public');

or

select * from like_in_tables('LOPES%','public');

Answer 3

The Workbench has this functionality, allows you to search for a content in all tables using WbGrepData.

The command would be like this:

WbGrepData -searchValue=ValorProcurado -tables=public.* -types=table;

Here the documentation: http://www.sql-workbench.eu/wbgrepdata_png.html
Only it is not possible to specify the type of Columa, but the search will be performed in all columns of all tables.