如何获取两个不同数据库中所有表的列表 [英] How to get a list of all tables in two different databases

问题描述

我正在尝试创建一个小的SQL脚本（在SQL Server Management Studio中）以获取两个不同数据库中所有表的列表。目的是找出两个数据库中都存在哪些表，而其中只有一个表中存在。

I'm trying to create a little SQL script (in SQL Server Management Studio) to get a list of all tables in two different databases. The goal is to find out which tables exist in both databases and which ones only exist in one of them.

我在SO上找到了各种脚本来列出所有的表。 一个数据库，但到目前为止，我无法获得多个数据库的表列表。

I have found various scripts on SO to list all the tables of one database, but so far I wasn't able to get a list of tables of multiple databases.

所以：有没有办法在SQL Server中查询特定数据库中的所有表，例如 SELECT * FROM ... WHERE databaseName ='first_db'，以便我可以将其与另一个数据库的结果结合起来？

So: is there a way to query SQL Server for all tables in a specific database, e.g. SELECT * FROM ... WHERE databaseName='first_db' so that I can join this with the result for another database?

推荐答案

SELECT * FROM database1.INFORMATION_SCHEMA.TABLES
UNION ALL
SELECT * FROM database2.INFORMATION_SCHEMA.TABLES

---

更新

为了比较两个列表，可以使用 FULL OUTER JOIN ，它将向您显示其中存在的表这两个数据库以及仅存在于其中一个数据库中的数据库：

In order to compare the two lists, you can use FULL OUTER JOIN, which will show you the tables that are present in both databases as well as those that are only present in one of them:

SELECT *
FROM database1.INFORMATION_SCHEMA.TABLES db1
  FULL JOIN database2.INFORMATION_SCHEMA.TABLES db2
    ON db1.TABLE_NAME = db2.TABLE_NAME
ORDER BY COALESCE(db1.TABLE_NAME, db2.TABLE_NAME)

您也可以添加 WHERE db1.TABLE_NAME为NULL或db2.TABLE_NAME为NULL 仅查看数据库之间的差异。

You can also add WHERE db1.TABLE_NAME IS NULL OR db2.TABLE_NAME IS NULL to see only the differences between the databases.

这篇关于如何获取两个不同数据库中所有表的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！