Get column data from multiple tables dynamically

user_6485753 0
Viewed 545

I have a list of tables for which I need to search if a single data point (userid) exists in any one table.

The following query gives list of tables with column userid:

select table_name
from information_schema.columns
where column_name = 'userid'

I would like to do a dynamic Select userid from table_1...table_n to show all the userids across these tables.

Expected psuedo query:

select userid, table_name from table_1...table_n
where userid = 'search'
information-schema sql-server sql
1 Answers

You need to use UNION ALL for this. (Avoid plain UNION because it does extra work to deduplicate its result set.) Yes, it makes for an unpleasantly verbose query.

You'll end up writing code to create a query that looks like this.

SELECT userid, 'table_1' table_name FROM table1
UNION ALL
SELECT userid, 'table_2' table_name FROM table2
UNION ALL
SELECT userid, 'table_3' table_name FROM table3
...

There is some good news buried here. If you create a view containing this dirty great query ...

CREATE OR REPLACE VIEW all_userids 
SELECT userid, 'table_1' table_name FROM table1
UNION ALL
SELECT userid, 'table_2' table_name FROM table2
UNION ALL
SELECT userid, 'table_3' table_name FROM table3

then do queries against the view like

SELECT userid FROM all_userids WHERE table_name = 'table_2'

the query optimizer knows what you're doing and avoids reading all the tables.

You can write a program to query the information_schema and generate this dirty great UNION ALL query. It's probably easiest to do that in a host language like Java / C# / php / whatever.

But you can also do it in SQL. Figure out how to write a query whose output is a query. Something like this might work. It uses STRING_AGG(val, separator) to generate the various SELECTs with UNION ALL between them. It then uses sp_executesql to run the query.

DECLARE @q NVARCHAR(MAX);

SELECT N'CREATE OR REPLACE VIEW all_userids ' +
       STRING_AGG 
         (N'SELECT userid, ''' + table_name + N''' table_name FROM ' + table_name , 
          N' UNION ALL ') + N';'
  INTO @q
  FROM information_schema.columns
 WHERE column_name = 'userid';
EXECUTE sp_executesql @q;

This is not, repeat not, debugged.

user_2056080
Related