How to search for something in 100% of Mysql database

Question

There is a Mysql query where I can search all the data of all tables in a database at one time?

Equivalent to something like this:

"SELECT todos_os_campos FROM todas_as_tabelas WHERE qualquer_campo = 'isso'"

Answer 1

In this Procedure it searches in all fields that are not numerical given content, first parameter you pass what you want to search and second the name of the database, it will return all found results. in separate queries, because there is no way to give a UNION ALL because the tables do not have the same number of columns. Follow the Code:

DROP PROCEDURE IF EXISTS sp_search_data;        

DELIMITER |

CREATE PROCEDURE sp_search_data(p_conteudo TEXT,                                                                        
                                p_nome_banco TEXT)
BEGIN

    DECLARE v_nome_tabela TEXT;
    DECLARE v_nome_coluna TEXT;
    DECLARE v_possui_registro INT;
    DECLARE v_fim INT DEFAULT 0;
    DECLARE cur_tabelas CURSOR FOR SELECT  TABLE_NAME,                                                                              
                                           COLUMN_NAME
                                    FROM information_schema.`COLUMNS`                                                                           
                                   WHERE TABLE_SCHEMA = p_nome_banco
                                     AND NUMERIC_PRECISION IS NULL;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fim = 1;

    OPEN cur_tabelas;

    REPEAT

        IF NOT v_fim THEN
        FETCH cur_tabelas INTO v_nome_tabela, v_nome_coluna;

        SET @v_possui_registro = 0;
        SET @v_select = CONCAT('SELECT COUNT(*) INTO @v_possui_registro
                                  FROM ',v_nome_tabela,'
                                 WHERE ', v_nome_coluna,' = "',p_conteudo,'"');

        PREPARE stmt_select FROM @v_select;
        EXECUTE stmt_select;
        DEALLOCATE PREPARE stmt_select;

        IF @v_possui_registro > 0 THEN

            SET @v_resultado = CONCAT('SELECT *
                                         FROM ',v_nome_tabela,'
                                        WHERE ', v_nome_coluna,' = "',p_conteudo,'"');
            PREPARE stmt_resultado FROM @v_resultado;
            EXECUTE stmt_resultado;
            DEALLOCATE PREPARE stmt_resultado;
        END IF;

    END IF;
   UNTIL v_fim END REPEAT;
   CLOSE cur_tabelas;


END
|
DELIMITER ;

-- CALL sp_search_data('TESTE', 'banco_teste');

Remembering that in this process can be made several implementations, as print the name of the table and column that was found the record, print the query that was made among other interesting things, is there as initial idea this structure.

Answer 2

Well, native man SQL does not possess this feature, but if you want to make a SELECT in all tables in a query you can only use the UNION to bring the records in a single query.

SELECT * FROM Tabela_1 WHERE condicao = 1
UNION  
SELECT * FROM Tabela_2 WHERE condicao = 2
UNION
SELECT * FROM Tabela_3 WHERE condicao = 3
UNION
SELECT * FROM Tabela_4 WHERE condicao = 4
UNION
...
// assim por diante com todas as tabelas

I know that way it can be unviable if you have 10,000 tables, but I give you this option as long as it is feasible for you to do so.