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?

here an answer : https://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them?rq=1 — , Oct 06 '17 at 00:41
@headmax . . . That is not the answer. You need to use dynamic SQL for this, essentially looping over all the tables. — Gordon Linoff, Oct 06 '17 at 00:42
@Gordon Linoff there are multiples situations TABLE_NAME, COLUMN_NAME isn't dynamic? — , Oct 06 '17 at 00: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, Oct 06 '17 at 00:54
It's a different approach and need some modification. Check it out: https://stackoverflow.com/a/27608824/2225030 — Harshil Doshi, Oct 06 '17 at 01:08

Joao Fernandes · Answer 1 · 2019-08-06T08:58:23.250

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).

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

1 Answers1