从其他查询的表中获取数据

Question

我正试图从另一个查询的所有表中获取数据，如下所示：

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

我现在得到的是5个表名，如"%Project%"。我想从这5个表中获取数据，而不仅仅是它们的名称。此外，我不想加入或联合这些表。我怎样才能做到这一点？

Answer 1

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