从n个表中选择*
有没有办法编写这样的查询：
select * from <some number of tables>
...表的数量未知？我想避免使用动态 SQL。我想从所有具有特定前缀的表中选择所有行：
select * from t1
select * from t2
select * from t3
...
我不知道可能有多少个 t(n) （可能是 1，可能是 20等）t 表的列结构不同。有些有 2 列，有些有 3 或 4 列。
使用动态 SQL 并不难，但我想知道是否有办法使用 sys.tables 之类的东西来做到这一点。
更新
基本数据库设计解释
N家公司将注册/登录到我的应用程序
每个公司将建立一个包含x列的表
（x取决于公司的业务类型，可以不同，例如想想两家公司：一家是木匠，另一家是报纸）
每家公司都会使用我构建的 API 填充自己的表
我对数据做什么：
我有一个“处理器”，可以是 SQL 或 C# 或任何。
如果一家公司至少有一行，我将在 COMMON 表中生成一条记录。
所以最终的结果将全部在一张表中。
来自这 N 个公司中任何一个的任何人都将登录并看到针对其自己公司筛选的 COMMON 表。
Is there a way to write a query like:
select * from <some number of tables>
...where the number of tables is unknown? I would like to avoid using dynamic SQL. I would like to select all rows from all the tables that (the tables) have a specific prefix:
select * from t1
select * from t2
select * from t3
...
I don't know how many t(n) might there be (might be 1, might be 20, etc.) The t table column structures are not the same. Some of them have 2 columns, some of them 3 or 4.
It would not be hard using dynamic SQL, but I wanted to know if there is a way to do this using something like sys.tables.
UPDATE
Basic database design explained
N companies will register/log in to my application
Each company will set up ONE table with x columns
(x depends on the type of business the company is, can be different, for example think of two companies: one is a Carpenter and the other is a Newspaper)
Each company will fill his own table using an API built by me
What I do with the data:
I have a "processor", that will be SQL or C# or whatever.
If there is at least one row for one company, I will generate a record in a COMMON table.
So the final results will be all in one table.
Anybody from any of those N companies will log in and will see the COMMON table filtered for his own company.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果没有动态 SQL，就无法做到这一点。拥有不同的表结构根本没有帮助。
更新
没有简单的方法可以在一个结果集中返回所需的输出（结果集至少具有与大多数列相同的表列数，甚至无法让我开始数据类型兼容性）。
但是，您应该检查@KM.的答案。这将带来多个结果集。
There would be no way to do that without Dynamic SQL. And having different table structures does not help that at all.
Update
There would be no easy way to return the desired output in one single result set (result set would have at least the same # of columns of the table with most columns and don't even get me started on data types compatibility).
However, you should check @KM.'s answer. That will bring multiple result sets.
列出您可以尝试的所有表：
您可以通过执行以下操作来可编程包含/排除表：
to list ALL tables you could try :
you can programmability include/exclude table by doing something like: