SQL checking if value exists through multiple different tables

Question

Lets say I have an unique 23-digit identifier of a file. I would like to check whether this identifier is present in any of the columns of all rows in all given tables. These tables are different as are their columns.

Is there some select which could help me achieve this?

Example scenario can be like this:

FileID = 534bde4c322755995941083

TableA (columnA, columnB, columnC)

TableB (columnD)

TableC (columnE, columnF)

I would like to return only those record which contain only this unique identifier and nothing more to it. The number of tables is various just like the columns.

Is there some SQL statement which could help me with this?

I can iterate through all records, columns, and tables but that seems to be like a huge overkill for such task.

The first thing that comes to mind is: Why would you have to do that? In all columns?? — Sergio, Apr 14 '14 at 15:38
Its hard to go to details, I have tried to simplified it, but unfortunately there is no other way around this. Its a CMS system and tables and columns are created through UI by users at their will, I also have no knowledge of what which columns represents. Well - I do have but then again I would need to iterate through those fields anyways. — user3370402, Apr 14 '14 at 15:43
Look at this question: http://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008 — Drunken Code Monkey, Apr 14 '14 at 22:41

M.Ali · Accepted Answer · 2014-04-14T22:27:41.520

1

you can use UNPIVOT something like this ...

SELECT Vals
FROM TableA A
 UNPIVOT( Vals FOR N IN (columnA, columnB, columnC)
         ) UP
WHERE Vals  = '534bde4c322755995941083'

UNION ALL 

SELECT ColumnD 
FROM TableB 
WHERE ColumnD =  '534bde4c322755995941083'

UNION ALL 

SELECT Vals
FROM TableC  C
 UNPIVOT( Vals FOR N IN (columnE, columnF)
         ) UP
WHERE Vals  =  '534bde4c322755995941083'

edited Apr 14 '14 at 22:27

answered Apr 14 '14 at 15:47

M.Ali

67,945
13
101
127

1

Except that implies that you know the table and column names, which the OP already stated he does not. See the comment above. – Drunken Code Monkey Apr 14 '14 at 22:42

score 0 · Answer 2 · answered Sep 07 '16 at 16:12

0

using phpmyadmin, click on menu 'search' enter your 23-digit , select all tables and you will get the occurences on each table

answered Sep 07 '16 at 16:12

Matoeil

6,851
11
54
77

SQL checking if value exists through multiple different tables

2 Answers2