Compare list of tables in two Databases from SSMS

Question

Post reply

Compare list of tables in two Databases from SSMS

syedathariqbal

Hall of Fame

Points: 3175
More actions
March 1, 2013 at 12:15 am

#394286

Hi SQL Gurus,
Need to do some cleansing work this morning and I was wondering If I can get help here in the forum.
Is there a way we can compare the list of
1) Tables
2) Views
3) SPs
in two different Databases on same server. I would use a 3rd party tool in case there is no way at all from SSMS.
The key here is that I don't want to compare the Data in tables, just checking table names would do.
So what I want is
Select <List of tables> in DB1
EXCEPT
SELECT <List of tables> in DB2
Same for view and SP.
Any Ideas, suggestions ? Thanks a lot in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

BriPan SSCrazy Points: 2061 More actions · Answer 1

Hi

I am not getting your question exactly

but do you want this kind of thing

select * from database1.sys.tables

where name not in (select name from database2.sys.tables)

syedathariqbal Hall of Fame Points: 3175 More actions · Answer 2

Managed to get the list using below query.

SELECT TABLE_NAME FROM [DB1].information_schema.TABLES

Where TABLE_TYPE='BASE TABLE'

EXCEPT

SELECT TABLE_NAME FROM [DB2].information_schema.TABLES

Where TABLE_TYPE='BASE TABLE'

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 3

You may want to use a full outer join rather than EXCEPT as you're only comparing in one direction.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

syedathariqbal (3/1/2013)
Managed to get the list using below query.
SELECT TABLE_NAME FROM [DB1].information_schema.TABLES
Where TABLE_TYPE='BASE TABLE'
EXCEPT
SELECT TABLE_NAME FROM [DB2].information_schema.TABLES
Where TABLE_TYPE='BASE TABLE'

In case there are other schemas:

SELECT TABLE_SCHEMA, TABLE_NAME FROM msdb.information_schema.TABLES

Where TABLE_TYPE='BASE TABLE'

EXCEPT

SELECT TABLE_SCHEMA, TABLE_NAME FROM master.information_schema.TABLES

Where TABLE_TYPE='BASE TABLE'

For Routines (procs and functions) - you could do something like this:

WITH x AS

(SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME

FROM msdb.INFORMATION_SCHEMA.ROUTINES

EXCEPT

SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME

FROM master.INFORMATION_SCHEMA.ROUTINES )

SELECT'msdb' [db],

x.SPECIFIC_SCHEMA [schema],

y.SPECIFIC_NAME [routineName],

y.ROUTINE_TYPE [routine],

y.ROUTINE_DEFINITION [ddl] -- note, this will be truncated at 4000 chars

FROM x

JOIN msdb.INFORMATION_SCHEMA.ROUTINES y

ON x.SPECIFIC_NAME=y.SPECIFIC_NAME

AND x.SPECIFIC_SCHEMA=y.SPECIFIC_SCHEMA

Edit: Typo

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001