How do I get a list of tables that don't appear in a different database?

Question

question

Vinodh asked Oct 19, '09

How do I get a list of tables that don't appear in a different database?

Hi, i need to get different tables by comparing two different databases. It should be in single select query.

For Example

select Name From DB1.sys.objects where type_desc='USER_TABLE'                      
EXCEPT                    
select Name From DB2.sys.objects where type_desc='USER_TABLE'                      
in this case i will get tables which are not present in the DB2

similarly to get vice versa,

select Name From DB2.sys.objects where type_desc='USER_TABLE'                      
EXCEPT                    
select Name From DB1.sys.objects where type_desc='USER_TABLE'

I need these two columns in single select.

sql-server-2005 meta-data

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-19T09:59:59Z

Kristen answered Oct 19, '09

Do you want a list of all tables in both database?

Or just the tables that are in only one of the databases?

This will list all tables and whether they are in DB1 or DB2 (or both)

            
SELECT TOP 10             
    [DB] = CASE WHEN T1.[Name] IS NOT NULL THEN 'DB1 ' ELSE '' END            
    	+ CASE WHEN T2.[Name] IS NOT NULL THEN 'DB2' ELSE '' END,            
    [Name] = COALESCE(T1.[Name], T2.[Name])            
FROM    DB1.sys.objects AS T1            
    FULL OUTER JOIN DB2.sys.objects AS T2            
    	 ON T2.[Name] = T1.[Name]            
    	AND T2.type_desc='USER_TABLE'            
WHERE       (T1.type_desc='USER_TABLE' OR T1.type_desc IS NULL)            
--            
ORDER BY COALESCE(T1.[Name], T2.[Name])

if you only want tables that are in only one of the databases then add this to the WHERE clause:

            
    AND (T1.[Name] IS NULL OR T2.[Name] IS NULL)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-10-19T09:07:47Z

Madhivanan answered Oct 19, '09

select Name From DB1.sys.objects where type_desc='USER_TABLE'            
EXCEPT select Name From DB2.sys.objects where type_desc='USER_TABLE'            
UNION ALL            
select Name From DB2.sys.objects where type_desc='USER_TABLE'            
EXCEPT select Name From DB1.sys.objects where type_desc='USER_TABLE'

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-10-19T15:46:23Z

Benjamin answered Oct 19, '09

The following query produces a single-column list of the tables not present in 1 of the 2 databases. You need to replace ONLY Your_Database1 and Your_Database2 to make it work. This script does not take into consideration the real possibility that 2 identically named tables could be different schemas and thus assumes your tables are all in the same schema. The use-case I envisioned was to produce a complete list of unsynchronized tables from either database.

--This version works on SQL Server 2005+

SELECT COALESCE(DB1.[name], DB2.[name]) AS [Unsynchronized Table List]            
FROM [Your_Database1].[sys].[objects] DB1            
FULL OUTER JOIN [Your_Database2].[sys].[objects] DB2 --give all rows from both tables            
ON DB1.[name] = DB2.[name] AND --the name must match            
    DB1.type = DB2.type --and the type must match (don't match system table name)            
WHERE COALESCE(DB1.type, DB2.type) = 'U' AND --only user tables            
    (DB1.[name] IS NULL OR DB2.[name] IS NULL) --only return rows that don't exist in 1 db            
ORDER BY            
    COALESCE(DB1.[name], DB2.[name]) --sort by name

--here is a version that works for SQL 2000

SELECT COALESCE(DB1.[name], DB2.[name]) AS [Unsynchronized Table List]            
FROM [Your_Database1]..[sysobjects] DB1            
FULL OUTER JOIN [Your_Database2]..[sysobjects] DB2 --give all rows from both tables            
ON DB1.[name] = DB2.[name] AND --the name must match            
    DB1.type = DB2.type --and the type must match (don't match system table name)            
WHERE COALESCE(DB1.type, DB2.type) = 'U' AND --only user tables            
    (DB1.[name] IS NULL OR DB2.[name] IS NULL) --only return rows that don't exist in 1 db            
ORDER BY            
    COALESCE(DB1.[name], DB2.[name]) --sort by name

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2009-11-17T21:14:25Z

Darren 2 answered Nov 17, '09

select name from db1..sysobjects where name not in ( select name from db2..sysobjects where xtype = 'u' ) and xtype = 'u'

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question