CODEWITHSUNDEEP
sql-serverdatabase-schema
M4N
M4N

Reputation: 96571

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

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?

Upvotes: 15

Views: 18057

Answers (3)

Andriy M
Andriy M

Reputation: 77687

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.

Upvotes: 25

M4N
M4N

Reputation: 96571

Just for completeness, this is the query I finally used (based on Andriy M's answer):

SELECT * FROM DB1.INFORMATION_SCHEMA.Tables db1
  LEFT OUTER JOIN DB2.INFORMATION_SCHEMA.Tables db2
    ON db1.TABLE_NAME = db2.TABLE_NAME
  ORDER BY db1.TABLE_NAME

To find out which tables exist in db2, but not in db1, replace the LEFT OUTER JOIN with a RIGHT OUTER JOIN.

Upvotes: 1

Andomar
Andomar

Reputation: 238126

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 ...

Upvotes: 3

Related Questions