0

I'm trying to get data from all tables that I have from another query as follows:

DECLARE @count int
SET @count = (SELECT COUNT(*) FROM (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%Project%') AS SUBQUERY)
WHILE(@count!=0)
    BEGIN
    SELECT * from (SELECT TABLE_NAME from (SELECT TABLE_NAME,
ROW_NUMBER() over (order by table_name) as row_number
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%Project%') as sub
WHERE row_number = @count) as another_sub;
    SET @count = @count-1
    end

What I get with this right now is 5 table names LIKE '%Project%'. I want to get the data from all of these 5 tables, not just their names. Also I don't want to join or union the tables. How can I achieve this?

warrior7
  • 5
  • 4
  • I assume the schema between each of these tables is the same? It looks like you want to union the results from any table with a name like "%Project%"? – Brian Hadley Sep 22 '22 at 15:50
  • If you account for the varying column names and their respective data types, you could `UNION` them all together. I'm assuming you want one result set? – jw11432 Sep 22 '22 at 15:50
  • I need separate results per table. The schema between each of these tables is the same. – warrior7 Sep 22 '22 at 15:52
  • Then why don't you just write separate select statements? I'm not understanding the issue. – jw11432 Sep 22 '22 at 15:54
  • I wrote separate select statements for every table name that I get through the while loop. But right now as a result I get only 5 table names, not their data. I need the data inside these tables. – warrior7 Sep 22 '22 at 15:56
  • Oh ok. I believe there's a system function that can handle this...`sp_executesql` may be what you want – jw11432 Sep 22 '22 at 15:59

1 Answers1

0
DECLARE @SQL varchar(max) = '';

SELECT @SQL = @SQL + 'SELECT * FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ';' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%Project%';

print @SQL;
--EXEC(@SQL);
Aardvark
  • 174
  • 1
  • 7
  • One more thing. How can I also include a where clause so it filters the results by specific keyword which may be found in each column of these tables? – warrior7 Sep 22 '22 at 16:25
  • Do not use variable coalescing to aggregate, it has undefined behaviour and can give you unexpected results. Use `STRING_AGG` or `FOR XML` instead. See https://stackoverflow.com/a/70491646/14868997 – Charlieface Sep 22 '22 at 19:59