Show table name where a value is present

Question

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

Yes it is perfectly possible, I have few of them in my personal toolbox eg search a date, string and decimal values etc. but it uses DB internal tables and bit slow as it runs cursors over each column of table but then comes people moaning about why it is in SQL Server or why you selecting system tables. — Surjit Samra, Oct 13 '11 at 07:05
Does this need to be flexible enough to work across data types, or just text, or what? Does the whole column value have to match? — grossvogel, Oct 13 '11 at 22:31
Basicaly it searches for a persons' id number. If it finds it in say 3 of the 5 tables. It displays the name of those 3 tables — SebastianOpperman, Oct 14 '11 at 06:31
If it's limited to a known set of columns in a schema you control, your best bet may be to write a query that explicitly searches those places. It may be a little tedious to write and maintain, but my generalized solution below does a lot of extra work to discover the columns to search. — grossvogel, Oct 14 '11 at 14:44

score 2 · Accepted Answer · answered Oct 13 '11 at 23:09

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

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, yeah, and it's ugly... Maybe it'll help you, though!

it's ugly but I think it's the only way to do it and might be helpful. +1 — Icarus, Oct 13 '11 at 23:19
how would i implement it into my php page? sorry it this a SQL query? — SebastianOpperman, Oct 14 '11 at 07:54
@SebastianOpperman: This script creates a stored procedure (whic requires mysql 5, by the way.) You'll need to run it once (via mysql client or phpMyAdmin) to create the procedure, then you'll need to call the procedure from your php script. If you've never worked with queries that return multiple results, you might also check out [this question](http://stackoverflow.com/questions/1683794/retrieving-multiple-result-sets-with-stored-procedure-in-php-mysqli). — grossvogel, Oct 14 '11 at 14:39

score 0 · Answer 2 · answered Oct 13 '11 at 07:45

0

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

answered Oct 13 '11 at 07:45

Ariful Islam

7,639
7
36
54

Hi, i do not know the names of the tables. Basically i want a query that searches through all the tables for a 'value' and displays the table names where that 'value' exists. – SebastianOpperman Oct 13 '11 at 10:01

Show table name where a value is present

2 Answers2