CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012information-schema
aryan
aryan

Reputation: 49

How to compare two database for unique tables?

I have an old SQL 2008 backup for our application. Today we encountered a case where we want to restore it as a current database in our sql-2012. I used the below query to count the tables in both database

USE YOURDBNAME
SELECT COUNT(*) from information_schema.tables 
WHERE table_type = 'base table'

my current database returned total 543 tables whereas old database showed 533 tables

How to find the unique ten tables from the current database by comparing it with the tables of my previous database. ?

Update 1

I used the below query to compare the tables but it returned no result 

select 'dbtest01' as dbname, t1.table_name
from dbtest01.[INFORMATION_SCHEMA].[tables] t1
where table_name not in (select t2.table_name
    from 
    dbtest02.[INFORMATION_SCHEMA].[tables] t2  
    )
union
select 'dbtest02' as dbname, t1.table_name
from dbtest02.[INFORMATION_SCHEMA].[tables] t1
where table_name not in (select t2.table_name
    from 
    dbtest01.[INFORMATION_SCHEMA].[tables] t2  
    )

Upvotes: 0

Views: 102

Answers (3)

Andrew O'Brien
Andrew O'Brien

Reputation: 1813

This query includes table_schema to make sure if the table name is repeated but in a different schema then it shows up. This shows tables that are in db_1 but not in db_2 as well as tables in db_2 but not in db_1.

SELECT
    db1.dbname AS db1_name,
    db1.table_schema AS db1_schema,
    db1.table_name AS db1_table,
    db2.dbname AS db2_name,
    db2.table_schema AS db2_schema,
    db2.table_name AS db2_table
FROM (
    SELECT
        'YOURDBNAME' AS dbname,
        table_schema,
        table_name
    FROM YOURDBNAME.information_schema.tables
    WHERE table_type = 'BASE TABLE'
    ) AS db1
FULL OUTER JOIN (
    SELECT
        'YOUROTHERDBNAME' AS dbname,
        table_schema,
        table_name
    FROM YOUROTHERDBNAME.information_schema.tables
    WHERE table_type = 'BASE TABLE'
    ) AS db2 ON
    db1.table_schema = db2.table_schema
    AND db1.table_name = db2.table_name
WHERE db1.dbname IS NULL
    OR db2.dbname IS NULL

Upvotes: 3

Philip Kelley
Philip Kelley

Reputation: 40319

These two queries will do the work, as a quick ad-hoc query. You have to do it twice, in "both directions", to catch all possible differences.

--  All tables in dbtest01 that are not in dbtest02
SELECT schema_name(schema_id), name
 from dbtest01.sys.tables
except select schema_name(schema_id), name
 from dbtest02.sys.tables
 order by name

--  All tables in dbtest02 that are not in dbtest01
SELECT schema_name(schema_id), name
 from dbtest01.sys.tables
except select schema_name(schema_id), name
 from dbtest02.sys.tables
 order by name

(Updated with schemas, based on @Andrew's reply. Full outer joins work too, but they hurt my brain.)

Upvotes: 2

sepupic
sepupic

Reputation: 8687

SELECT table_name from dbtest01.information_schema.tables 
WHERE table_type = 'base table' 

EXCEPT

SELECT table_name from dbtest02.information_schema.tables 
WHERE table_type = 'base table'

Upvotes: 1

Related Questions