Get data from tables that I have from another query-CodePudding

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?

CodePudding user response：

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);