Search entire database(210 tables) for a unique Id (PK) and get the table name

Question

Rewritten Question:

I need to look through an entire database, 210 tables for a unique ID that is a number.

I have limited access to my database through things called nodes (Visual programming.)

These nodes allow for simple querys like SELECT x FROM x WHERE x I believe I can use most commands here.

I'm unfamilar with sql and thought I might be able to get some help here.

Below you can see an example of a node where I can type in commands.

and below that my database structure.

What i'm looking to do is look through the entire database for a unique number, this number changes dynamically but can always be found in the "Id" column of every table. This column is also a Primary Key.

By searching through the database and finding this dynamic unique number in one of the 210 tables I then need to be able to get the table name that the data is located in and the row information ie; all the different column entries for that row.

Example:

more info:

Answer 1

In your query:

SELECT table_name 
FROM information_schema.columns 
WHERE (:uniqueid ) IN (SELECT 'Id' 
                       FROM information_schema.columns 
                       WHERE 'Id' = (:uniqueid ))

you are trying to mix data and meta-data. information_schema contains only meta-data so you won't find :uniqueid in there. You will have to extract meta-data first, and then iterate over that some how. Assuming all columns are named id (which is a bad choice, but nothing you can avoid if I get it right), something like the following pseudo-code:

c1.execute("SELECT table_name FROM information_schema.columns WHERE column_name = id")
for row in c1.fetchall():
    t = row[0]
    c2.execute("SELECT 1 FROM %s WHERE id = :uniqueid" % (t) )
    if c2.fetchone():
        print "Found %s in %s" %  (:uniqueid, t)
        sys.exit(0)

will print the first table where there is an id = :uniqueid

Another option is to generate a union all for all tables:

SELECT 't1' from t1 where id = :uniqueid
UNION ALL
SELECT 't2' from t2 where id = :uniqueid
UNION ALL
...
UNION ALL 
SELECT 't210' from t210 where id = :uniqueid