Return to Answer

added 50 characters in body; deleted 12 characters in body; added 2 characters in body

edited Aug 6, 2019 at 8:58

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 (if empty means it  --doesn't meet the 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).

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 (if empty means it  --doesn't meet the 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; RAISE NOTICE '%',rowf; 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).

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

added 9 characters in body

Source Link

edited Aug 2, 2019 at 16:07

Joao Fernandes

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 (if empty means it --doesn't meet the 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; RAISE NOTICE '%',rowf; 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).

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 (if empty means it --doesn't meet the 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; RAISE NOTICE '%',rowf; 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).

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 (if empty means it --doesn't meet the 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; RAISE NOTICE '%',rowf; 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).

added 1 character in body; added 6 characters in body; added 5 characters in body

Source Link

edited Aug 2, 2019 at 16:01

Joao Fernandes

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 (if empty means it --doesn't meet the 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', 'tbls''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''''%6172817''' ')) INTO rowf; RAISE NOTICE '%',rowf; 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).

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 (if empty means it --doesn't meet the 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', 'tbls') --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; RAISE NOTICE '%',rowf; 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 to scroll through the result text).

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 (if empty means it --doesn't meet the 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; RAISE NOTICE '%',rowf; 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).

Source Link

answered Aug 2, 2019 at 15:56

Joao Fernandes

Collectives™ on Stack Overflow

Return to Answer