Find a column in one of many tables given only one value?

Question

I have this:

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name = 'whatever'

but what I need is something like this:

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_data = 'whatever'

So, in words, I have a value and I have no idea where it's stored. Is there a way to literally check the entire database and return the table, column?

aaaand yes, I know, db admins wouldn't be happy!

If you know which columns are there (or in the better case, where you generate the queries programatically), you could use `OR` and check each column separately. — Eran Zimmerman Gonen, Aug 10 '12 at 18:16

score 1 · Answer 1 · edited May 23 '17 at 11:48

This might get you going in the right direction.

1. Create find_column stored procedure

DROP PROCEDURE IF EXISTS `find_column`;

DELIMITER $$

CREATE PROCEDURE `find_column`(IN i_value varchar(200),
                               OUT o_columns varchar(2000),
                               OUT o_message varchar(500))
MAIN_BLOCK : BEGIN

 DECLARE is_numeric boolean;

 CHECK_NUMERIC : BEGIN
   set is_numeric = i_value REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';
 END CHECK_NUMERIC;


 FIND_IT : BEGIN

   DECLARE bNoMoreRows BOOLEAN DEFAULT FALSE;
   DECLARE v_schema varchar(64);
   DECLARE v_table varchar(64);
   DECLARE v_column varchar(64);
   DECLARE v_data_type varchar(64);
   DECLARE v_count int;


   -- all schemas, tables and columns in DB
   DECLARE columns CURSOR FOR
   select table_schema,table_name,column_name,data_type from information_schema.columns;

   DECLARE EXIT HANDLER for SQLEXCEPTION set o_message := concat('Unexpected error while trying to find schema, table and column for value : ',i_value);
   declare continue handler for not found set bNoMoreRows := true; 

   open columns;

   set o_columns = "";

   COLUMN_LOOP: loop
    fetch columns
    into v_schema,v_table,v_column,v_data_type;

    if (
        (v_data_type in ('int','bigint','tinyint','decimal','smallint','mediumint')      and is_numeric=1)
     or (v_data_type not in ('int','bigint','tinyint','decimal','smallint','mediumint') and is_numeric=0)
       )
     then 


     SET @dyn_sql=CONCAT('select count(*) into @c from `',v_schema,'`.`',v_table,'` where `',v_column,'`=?');

     SET @c = 0;
     SET @v_value = i_value;
     PREPARE stmt FROM @dyn_sql;
     EXECUTE stmt using @v_value;
     DEALLOCATE PREPARE stmt; 

     SET v_count = @c;

     if v_count > 0 then

       if length(o_columns <= 1800) then 
        set o_columns = concat(o_columns,",",v_schema,".",v_table,".",v_column);
        end if;
     end if;
    end if;

     if bNoMoreRows then
      set o_columns = substring(o_columns,2);
      close columns;
     leave COLUMN_LOOP;
    end if;

   END loop COLUMN_LOOP;

 END FIND_IT;

END MAIN_BLOCK$$

DELIMITER ;

2. Call find_column stored procedure with your value

call `find_column`('whatever',@columns,@message);

3. Check out the results

select @columns;

The is_numeric bit is lovingly ripped-off JBB's answer from this post.

It ain't perfect (what happens if the number of columns that your value exists exceeds 10 or so? If that is the case then this will only return the first 10 or so columns (depends on how long the schema.table.column name string is).

Hopefully it'll get you going in the correct direction.

An you're right. You're DB admins will be unhappy with you. But if you don't annoy them once in a while then you're not trying hard enough IMHO ;-)

Good luck.

Find a column in one of many tables given only one value?

1 Answers1