CODEWITHSUNDEEP
sql-serverdatabasesql-server-2012database-schemainformation-schema
Alok Singh
Alok Singh

Reputation: 174

SQL Server : finding list of unique tables across databases

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:

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

Upvotes: 0

Views: 131

Answers (3)

Alok Singh
Alok Singh

Reputation: 174

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

Upvotes: 3

ahmed abdelqader
ahmed abdelqader

Reputation: 3560

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269943

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.

Upvotes: 2

Related Questions