Is it possible to search a data in the database without knowing which table it is in?

Question

I have a banco de dados very large and it is impossible to build a SELECT * FROM for each table and locate the data I need.

It is a texto especifico ('with as parameter 23') I’ve searched every way and no found.

It is possible to make a query for this? how should I search? I am using a relational database, in a non-relational would be easier?

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 ',p_nome_banco,'.',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 ',p_nome_banco,'.',v_nome_tabela,'
                                        WHERE ', v_nome_coluna,' = "',p_conteudo,'"');
            PREPARE stmt_resultado FROM @v_resultado;
            EXECUTE stmt_resultado;
            DEALLOCATE PREPARE stmt_resultado;
                        SELECT CONCAT(p_nome_banco,'.',v_nome_tabela);
        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.

In this case in the first result tab it returns the row that found the record and in the second tab the table.