I have banco de dados too large and it is impractical to build a SELECT * FROM for each table and find the data I need.
If it is a
texto especifico ('with as parameter 23')I have already looked in all ways and not I found.
Is it possible to make a query for this? how should I look? I am using a relational database, in a non relational would it be easier?
In this procedure it looks in all the fields that are not numeric certain content, first parameter you pass what you want to search and in the second the name of the database, it will return all the results found. in separate queries, because you can not 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');
Remember that in this procedure you can make several implementations, such as printing the name of the table and column that was found in the registry, printing the query that was done among other interesting things,
In this case in the first result tab it returns the line that found the record and in the second tab the table.