Search a db for all tables that have a certain value in a column

Question

Is it possible to search all tables in a DB for a certain value in a column? I have 30 tables in my DB. Not all of them are using the FK employee_no. Out of all the tables that do contain an employee_no column, not all tables will have a record entered for every employee.

I would like to get a list of all the tables that contain the value 6172817 for the employee_no column.

I know

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like '%employee_no'

will return all the tables with the column name employee_no, but now I want all the tables with the value 6172817 for employee_No

I have tried

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE employee_no like '%6172817'

Is this possible?

@headmax . . . That is not the answer. You need to use dynamic SQL for this, essentially looping over all the tables. — Gordon Linoff
– Gordon Linoff, Commented Oct 6, 2017 at 0:42
@Gordon Linoff there are multiples situations TABLE_NAME, COLUMN_NAME isn't dynamic? — user8556290
– user8556290, Commented Oct 6, 2017 at 0:49
@headmax Information_Schema will only give name of tables having employee_no as column. You need to iterate through all those tables to check for particular employee_no given in question. — Harshil Doshi
– Harshil Doshi, Commented Oct 6, 2017 at 0:54

Joao Fernandes · Accepted Answer · 2019-08-06 08:58:23Z

So this is what i got so far (made in postegresql though so you'll need to convert to mysql):

DO $$ DECLARE rowt text; -- cursor retun rowf text; -- name of the current table that meets our requirement rowfz text; -- formated rout cr CURSOR FOR (SELECT t.table_name::text FROM information_schema.tables t INNER JOIN information_schema.columns c ON c.table_name = t.table_name AND c.table_schema = t.table_schema WHERE c.column_name = 'employee_no' -- The column you're looking for here AND t.table_schema NOT IN ('information_schema', 'my_db') -- Add any unwanted DB's -- separated by comas AND t.table_type = 'BASE TABLE' ORDER BY t.table_name); BEGIN FOR rowt IN cr LOOP rowfz := REPLACE (rowfz::text,'(',''); rowfz := REPLACE (rowfz::text,')',''); EXECUTE (concat(' SELECT ''',rowfz, ''' FROM ', rowfz,' WHERE ', rowfz, '.employee_no LIKE '''%6172817''' ')) INTO rowf; IF rowf IS NOT NULL -- prints out only the valid(not null) table names RAISE NOTICE '%',rowf; END IF; END LOOP; END $$;

This will tell you exactly what tables have what you want, however it won't be shown in a neat looking table(you might need to scroll through the result text).

Collectives™ on Stack Overflow

Search a db for all tables that have a certain value in a column

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related