我有很多数据库有相同的表，如何从所有表中获取值？没有一个一个选择。 [英] I have many database have the same table , How to get values from all of that table ? Without select one by one .

问题描述

我有许多数据库，例如：

- BR_1

- BR_2

- BR_3

- 。 ....



我想从所有数据库中选择Table.Students

没有逐一选择：



SELECT * FROM BR_1.dbo.Students

SELECT * FROM BR_2.dbo.Students

SELECT * FROM BR_3。 dbo.Students

i Have many datatabase such as :
- BR_1
- BR_2
- BR_3
- .....

and i want to select Table.Students from all of that databases
without select one by one :

SELECT * FROM BR_1.dbo.Students
SELECT * FROM BR_2.dbo.Students
SELECT * FROM BR_3.dbo.Students

推荐答案

尝试创建存储过程，

1.首先，您需要搜索具有匹配名称的数据库中的表，示例

try to create stored procedure,
1. first you need to search tables in the databases with matching name,for example

sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''Students'''

找到学生包含的数据库。请参阅查找跨所有数据库的SQL Server上的表 [ ^ ]



2.现在你需要根据存储过程中的上述数据库和表值构建union sql语句。例如，如果你有BR_1，BR_2，你的sql字符串将是

find you the databases where Students contains. refer Find a Table on a SQL Server across all Databases[^]

2. Now you need to build union sql statement based on above database and table values in your stored procedure. for example if you have BR_1, BR_2, your sql string will be

SELECT * FROM BR_1.dbo.Students
union
SELECT * FROM BR_2.dbo.Students

3.最后执行动态创建的sql语句并获取数据

3. finally execute the dynamically created sql statement and fetch the data

这篇关于我有很多数据库有相同的表，如何从所有表中获取值？没有一个一个选择。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！