如何从MS SQL 2008中id = 1的每个表中检索所有记录 [英] How to retrieve all record from every table where id = 1 in MS SQL 2008

问题描述

如何从SQL Server 2008 R2中的单个数据库（EX：database1）中检索每个表中的所有记录（例如：table1，table2，table3，... tableN），其中id = 1



假设我有1个数据库，并且有无限的表（EX.table1，table2，....，tableN）。这样就可以从每个表上的id = 1的整个数据库中获取所有记录。我可以使用SQL information_schema.table或information_schema.column，但我不知道如何使用它。



任何帮助表示赞赏



感谢高级



< b>我尝试了什么：



我尝试使用information_schema.table和information_schema.column

How to retrieve all record from every table (Ex: table1, table2, table3, ... tableN) where id = 1 from single database (EX: database1) in SQL Server 2008 R2

Suppose,I have 1 database and in that have infinite tables (EX. table1,table2, ....,tableN). So is this possible to get all the record from entire database where id=1 on each table.I thing it is possible with SQL information_schema.table or information_schema.column but i don't know how to use this.

Any help appreciated

Thanks in Advanced

What I have tried:

I have try using information_schema.table and information_schema.column

推荐答案

这是可能的 - 但每次添加或删除表时查询都会有所不同，这是一个非常不寻常的尝试 - 它几乎肯定意味着你的整个数据库设计是错误的：SELECT命令是设计的返回来自一个基表的信息，以及来自其他相关表的共享信息（通常通过FOREIGN KEY关系）。

尝试从可变数量的表中返回不相关的信息并不是真的同样的事情。



但是如果ID是一个普通的访问者，那么你需要这样的东西：

It's possible - but the query would be different each time a table is added or removed, and it's a very unusual thing to try and do - it almost certainly means that your whole DB design is wrong: SELECT commands are designed to return information from one base table, with addition information from other related tabled which share common information (normally via a FOREIGN KEY relationship).
Trying to return unrelated info from a variable number of tables is not really the same thing.

But if the ID is a common accessor, then you'd need something like:

SELECT a.*, b.*, c.*, d.*, ...
FROM Table1 a
JOIN Table2 b ON a.ID = b.ID
JOIN Table3 c ON a.ID = c.ID
JOIN Table4 d ON a.ID = d.ID
...
WHERE a.ID = 1

但正如我所说 - 如果是这种情况，通常你的数据库设计得很糟糕。

But as I say - if that's the case, it's normally your DB that is badly designed.

这篇关于如何从MS SQL 2008中id = 1的每个表中检索所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！