Show the name of the table in which the value is present

Question

Is it possible to show the table name in db where a specific value is present. I have different tables, and I want to show only the names of tables that contain a specific value in any of the fields.

Answer 1

This will return many empty result sets, but non-empty ones match the table / column combinations that match your search. It works only for text and detects columns containing a value (as opposed to a complete match of columns).

DELIMITER | DROP PROCEDURE IF EXISTS `SearchAllTables`| CREATE PROCEDURE `SearchAllTables` ( IN _search varchar(256) ) LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER BEGIN -- declare stuff declare _tableName varchar(64); declare _columnName varchar(64); declare _done tinyint(1) default 0; -- we will examine every string column in the database declare _columnCursor cursor for select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = database() and (DATA_TYPE like '%char%' or DATA_TYPE like 'text'); declare CONTINUE handler for NOT FOUND SET _done = 1; OPEN _columnCursor; LOOP1: LOOP -- get the next table/column combination FETCH _columnCursor INTO _tableName,_columnName; IF _done = 1 THEN CLOSE _columnCursor; LEAVE LOOP1; END IF; -- query the current column to see if it holds the value SET @query = concat( "select '",_tableName,"' as TableName, '", _columnName,"' as ColumnName from ",_tableName," where ",_columnName," like concat('%',?,'%') group by 1;" ); SET @search = _search; PREPARE _stmt FROM @query; EXECUTE _stmt USING @search; DEALLOCATE PREPARE _stmt; END LOOP LOOP1; END| DELIMITER ; 

Oh yes, and it's ugly ... Maybe it will help you!

Answer 2

 SELECT TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'database_name' AND COLUMN_NAME = 'column_name'