CODEWITHSUNDEEP
mysqlsqldatabaseinformation-schema
αԋɱҽԃ αмєяιcαη
αԋɱҽԃ αмєяιcαη

Reputation: 11515

MySQL find column name where it's value match the pattern

How to search the entire database for column name equal to my rule and specific value as well.

Let's say that i want to search for column name like voucher where it's value contain that word value10

So far i can find the column name but i don't know how to match with value as well.

SELECT column_name FROM information_schema.columns 
WHERE TABLE_SCHEMA = 'dbname' AND column_name LIKE '%voucher%'

So the end goal is to find any column name like voucher containing value10 within it's content.

Upvotes: 1

Views: 2600

Answers (2)

Akina
Akina

Reputation: 42632

Procedure code:

CREATE PROCEDURE search_tables ( IN column_pattern TEXT, 
                                 IN value_pattern TEXT )
BEGIN
SELECT GROUP_CONCAT (CONCAT( ' SELECT ''', 
                             TABLE_NAME, 
                             '.', 
                             COLUMN_NAME, 
                             ''' AS `table.column`, ', 
                             COLUMN_NAME, 
                             ' AS `value`\nFROM ', 
                             TABLE_NAME,
                             '\nWHERE ',  
                             COLUMN_NAME, 
                             ' LIKE ''', 
                             value_pattern, 
                             '''' )
                     SEPARATOR ' UNION ALL ')
INTO @query
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE column_pattern
  AND TABLE_SCHEMA = DATABASE();
PREPARE stmt FROM @query;
EXECUTE stmt;
DROP PREPARE stmt;
END

Test tables:

CREATE TABLE table1 (val1 VARCHAR(8), val2 TEXT);
INSERT INTO table1 VALUES 
('a_01_a','b_11_b'), 
('c_211_c','d_311_d'), 
('e_55_e','f_00_f');

CREATE TABLE table2 (val3 CHAR(6), field4 VARCHAR(64));
INSERT INTO table2 VALUES 
('x_1123','ghjghj_11_tyuyu'), 
('8901_t','sdf_SDF_sdf');

Call:

CALL search_tables('%val%', '%11%');

Output:

table.column       value

table1.val1        c_211_c
table1.val2        b_11_b
table1.val2        d_311_d
table2.val3        x_1123

fiddle

Upvotes: 1

VN'sCorner
VN'sCorner

Reputation: 1552

Create a stored procedure to loop through meta data table INFORMATION_SCHEMA to fetch all tables with column_name of choice. Further dynamic SQL is used to scan each of the tables retrieved for columns having the value of choice.

DDL and DML for setting the data for testing :

create table TESTA(COLMNA char(255),COLMNC char(255));
create table TESTB(COLMNA char(255),COLMNB char(255));
create table TESTC(COLMND char(255),COLMNA char(255));

insert into TESTA values('value0','someothercolmn');
insert into TESTB values('value0','anothersomeothercolmn');
insert into TESTB values('value1','Yetanothercolumn');

Test is to search all tables having column_name as COLMNA with value as value0. The procedure will accept column_name and column_Value, hence can be used across the database, just need to pass values as appropriate.

CREATE PROCEDURE Findtables( colmn_name VARCHAR(64),colmn_value VARCHAR(64) )

BEGIN

   DECLARE tablename CHAR(64);

   DECLARE c1 CURSOR FOR
     SELECT table_name
     FROM information_Schema
     WHERE column_name = colmn_name;

   OPEN c1;
    lable_loop:LOOP
   FETCH c1 INTO tablename;

     select tablename;
     SET @sql = CONCAT('SELECT * FROM  ', tablename, ' WHERE ',colmn_name,' = "',colmn_value ,'" ;'); 

        PREPARE stmt FROM @sql;
        EXECUTE stmt; 
        DEALLOCATE PREPARE stmt;

     END LOOP lable_loop;  
   CLOSE c1;

END;

Call the stored procedure :

CALL Findtables('COLMNA','value0');

Output :

tablename
TESTA
COLMNA  COLMNC
value0  someothercolmn
tablename
TESTB
COLMNA  COLMNB
value0  anothersomeothercolmn
tablename
TESTC
COLMND  COLMNA

Demonstration of the solution can be found in DBFIDDLE link [https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4888a6160faf97fb75665832d6610293][1]

PS : I have to create INFORMATION_SCHEMA table in dbfiddle as metadata tables are not accessible.

Upvotes: 0

Related Questions