Search for a String and a Number at all tables in a Database

Question

I tried to search for some days in several places but didn't succeed with this script. I need to search for a String(NVarChar) and a Number(Int) in the same record, at all tables in a Database. In the following example, I need to locate the Table and the Field where both criteria are present. For example, find AAA string but only if a field with 990 number is present.

TBL_TEST

FIELD_A    |    FIELD_B    |    FIELD_C   |
AAA        |     124       |      12      |
BBB        |     457       |      12      |
CCC        |     347       |      12      |
AAA        |     990       |      13      | <---
BBB        |     387       |      13      |
CCC        |     213       |      13      |
AAA        |     888       |      14      |
BBB        |     888       |      14      |
CCC        |     888       |      14      |

****** I found this script at this great place but it only searches for string data and only one occurrence, but I need to find a String and a Number and only records where both records are available.

StackOverFlow

Let me try to explain me better, hope my English helps. Imagine a have a Database with 3 Tables (TBL1, TBL2 and TBL3). These tables have several columns, but in this example I will only place 3 columns per table.

TBL1

FIELD_A    |    FIELD_B    |    FIELD_C   |
AAA        |     124       |      132     |
BBB        |     457       |      1E2     |
CCC        |     347       |      1E2     |
AAA        |     KK0       |      13W     | 
BBB        |     387       |      136     |
CCC        |     213       |      133     |
AAA        |     888       |      990     |<---
BBB        |     888       |      144     |
CCC        |     888       |      14      |

TBL2

FIELD_A    |    FIELD_B    |    FIELD_C   |
AAA        |     ASD       |      12      |
BBB        |     3ED       |      12      |
CCC        |     32E       |      12      |
AAA        |     990       |      13      |<---
BBB        |     123       |      13      |
CCC        |     213       |      13      |
AAA        |     445       |      14      |
BBB        |     234       |      14      |
CCC        |     A23       |      14      |

TBL3

FIELD_A    |    FIELD_B    |    FIELD_C   |
AAA        |     124       |      132     |
BBB        |     457       |      990     |
CCC        |     347       |      1E2     |
AAA        |     KK0       |      13W     | 
BBB        |     387       |      136     |
CCC        |     213       |      133     |
AAA        |     888       |      990     |
BBB        |     888       |      144     |
990        |     888       |      AAA     |<---

The result of the script must be something like this.

TBL1
FIELD_A / FIELD_C
TBL2
FIELD_A / FIELD_B
TBL3
FIELD_A / FIELD_C

Hope to be clear enough. Thank you once more.

With that sample table data, what is the expected result? Does it matter if 990 is in the column FIELD_B or in the column FIELD_C? — jarlh, Oct 25 '18 at 07:40
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Oct 25 '18 at 08:36
Hi Jarlh, the expected result is the one in the fourth row where you can see the arrow at the end. It doesn't matter if it is in column A,B,C ... or N. I expect that it indicates me in which Table and Column were the results found. — Mharugha, Oct 25 '18 at 22:07

Fahmi · Answer 1 · 2018-10-25T07:41:49.557

0

Use where clause for both condition using AND operator

select * from tablename where FIELD_A='AAA' and FIELD_B=990

edited Oct 25 '18 at 07:41

answered Oct 25 '18 at 07:40

Fahmi

37,315
5
22
31

1

You probably mean FIELD_B=990 – jarlh Oct 25 '18 at 07:41
Hi, the script I was looking for, is the one that searches at all Tables inside a Database. – Mharugha Oct 25 '18 at 22:10

score 0 · Answer 2 · answered Oct 25 '18 at 07:40

use corelated subquery

select * from  your_table t 
                where exists ( select 1 from your_table t1
                                             where t1.FIELD_A=t.FIELD_A and
                                                         t1.FIELD_B=990
                             )

score 0 · Answer 3 · answered Oct 25 '18 at 11:09

You need dynamic search of two values in all table, Take this simple example(try Paste and execute):

DECLARE @SQL AS nvarchar(MAX)
    SET @SQL = ''
    Select  @SQL = @SQL + 'Select * from TBL_TEST where '+ISC.COLUMN_NAME+'= ''AAA'' AND '+(Select top(1) C.COLUMN_NAME FROM 
        INFORMATION_SCHEMA.TABLES T
        INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME
    where IST.TABLE_NAME = 'TBL_TEST' and C.TABLE_NAME ='TBL_TEST'  and c.COLUMN_NAME<>ISC.COLUMN_NAME)+'= ''990'''
    FROM 
        INFORMATION_SCHEMA.TABLES IST
        INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC ON IST.TABLE_NAME = ISC.TABLE_NAME
    where IST.TABLE_NAME = 'TBL_TEST'

    exec(@SQL)

Search for a String and a Number at all tables in a Database

3 Answers3