15

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?

M4N
  • 94,805
  • 45
  • 217
  • 260

3 Answers3

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

Andriy M
  • 76,112
  • 17
  • 94
  • 154
  • +1 Cool, I could have sworn this didn't work! Even works with the SQL Server specific `db1.sys.tables`. – Andomar Jul 04 '11 at 07:01
3

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 ...
Andomar
  • 232,371
  • 49
  • 380
  • 404
1

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.

M4N
  • 94,805
  • 45
  • 217
  • 260