SQL Server : finding list of unique tables across databases

Question

I have around 21 databases on my SQL Server 2012 machine, which should ideally have the same list of tables but they don't.

Assume:

Database 1 has tables A,B,C,D,E
Database 2 has tables A,B,C,D,Z
Database 3 has tables A,B,C,Y,Z
Database 4 has tables A,B,X,Y,Z

The output of my final query must be a table list like A,B,C,D,E,X,Y,Z

I understand this information can be sourced by joining sys.tables of each database, but I am not sure about which join should I use and how.

Any starting point will be appreciated

[This](http://stackoverflow.com/questions/6568098/how-to-get-a-list-of-all-tables-in-two-different-databases?rq=1) is something similar I found but I need to do the same thing for multiple databases — Alok Singh, Aug 02 '16 at 12:24

score 3 · Accepted Answer · answered Aug 02 '16 at 12:43

Just for those who stumble upon this in future.

SET NOCOUNT ON
DECLARE @AllTables TABLE
        (
         ServerName NVARCHAR(200)
        ,DBName NVARCHAR(200)
        ,SchemaName NVARCHAR(200)
        ,TableName NVARCHAR(200)
        )
DECLARE @SearchSvr NVARCHAR(200)
       ,@SearchDB NVARCHAR(200)
       ,@SearchS NVARCHAR(200)
       ,@SearchTbl NVARCHAR(200)
       ,@SQL NVARCHAR(4000)


SET @SearchSvr = NULL  --Search for Servers, NULL for all Servers
SET @SearchDB = NULL --Search for DB, NULL for all Databases
SET @SearchS = NULL  --Search for Schemas, NULL for all Schemas
SET @SearchTbl = NULL  --Search for Tables, NULL for all Tables


SET @SQL = 'SELECT  
        @@SERVERNAME
        ,''?''
        ,s.name
        ,t.name
         FROM [?].sys.tables t 
         JOIN sys.schemas s on t.schema_id=s.schema_id 
         WHERE @@SERVERNAME LIKE ''%' + ISNULL(@SearchSvr, '') + '%''
         AND ''?'' LIKE ''%' + ISNULL(@SearchDB, '') + '%''
         AND s.name LIKE ''%' + ISNULL(@SearchS, '') + '%''
         AND t.name LIKE ''%' + ISNULL(@SearchTbl, '') + '%''
         AND ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'',''SSISDB'')

           '
-- Remove the '--' from the last statement in the WHERE clause to exclude system tables


INSERT  INTO @AllTables
        (
         ServerName
        ,DBName
        ,SchemaName
        ,TableName
        )
        EXEC sp_MSforeachdb @SQL
SET NOCOUNT OFF
SELECT distinct tablename 
FROM    @AllTables

https://stackoverflow.com/questions/2875768/how-do%20-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set — John, Oct 12 '17 at 20:48

score 2 · Answer 2 · answered Aug 02 '16 at 12:29

You can use union:

select d.*
from ((select table_name from db1.information_schema.tables) union
      (select table_name from db2.information_schema.tables) union
      . . .
      (select table_name from db21.information_schema.tables) 
     ) d;

Actually, the subquery is not really necessary, but it is handy if you want to do something such as count the number of times that each table appears.

score 0 · Answer 3 · answered Aug 02 '16 at 12:30

0

If I understood your question correctly,

You want to list all the tables for all databases

There is avery simple script which will accomplish this task

as floowing:

sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'

and getting the list of tables only :

sp_msforeachdb 'select "?" AS db, name from [?].sys.tables'

hope this helps

answered Aug 02 '16 at 12:30

ahmed abdelqader

3,409
17
36

Unique table 'Across' databases. – Alok Singh Oct 19 '16 at 12:52

SQL Server : finding list of unique tables across databases

3 Answers3