How to get a list of all tables in two different databases

Question

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.

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.

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?

Answer 1

As far as I know, you can only query tables for the active database. But you could store them in a temporary table, and join the result:

use db1
insert #TableList select (...) from sys.tables
use db2
insert #TableList2 select (...) from sys.tables
select * from #TableList tl1 join Tablelist2 tl2 on ...

Answer 2

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

---

UPDATE

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)

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