How to find common tables between two databases?

Question

How to find common tables between two databases?

sqlpanther

Ten Centuries

Points: 1204
More actions
April 10, 2013 at 2:13 am

#394557

Hello All,
Can someone tell me how can we find common tables between 2 databases? Any scripts or querry plzz.
thanks in advance...

Viewing 11 posts - 1 through 10 (of 10 total)

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

demonfox SSCertifiable Points: 6289 More actions · Answer 1

sqlpanther (4/10/2013)
Hello All,
Can someone tell me how can we find common tables between 2 databases? Any scripts or querry plzz.
thanks in advance...

common table , by name , by attributes or by data ?

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

sqlpanther Ten Centuries Points: 1204 More actions · Answer 2

Yes with common tables, attibutes..no need for data..

crashdan SSCommitted Points: 1990 More actions · Answer 3

if it just for a quick look - you could query sys.objects / sys.columns / sys.tables for what you want, then run the same query over linked server and put it into a temp table and do your analysis from there

David McKinney SSChampion Points: 10472 More actions · Answer 4

David McKinney

SSChampion

Points: 10472

April 10, 2013 at 7:29 am

#1605220

Are the databases on the same server?

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

Here you go.

http://lmgtfy.com/?q=sql+server+2008+script+to+compare+databases

Lowell SSC Guru Points: 323504 More actions · Answer 6

here's a really easy example:

--common tables between the two

SELECT name from SandBox.sys.tables

INTERSECT

SELECT name from Dictionary.sys.tables

--tables only in SandBox

SELECT name from SandBox.sys.tables

EXCEPT

SELECT name from Dictionary.sys.tables

--tables only in Dictionary

SELECT name from Dictionary.sys.tables

EXCEPT

SELECT name from SandBox.sys.tables

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlpanther Ten Centuries Points: 1204 More actions · Answer 7

sqlpanther

Ten Centuries

Points: 1204

April 10, 2013 at 9:08 am

#1605278

Yes all the objects are in the same server

David McKinney SSChampion Points: 10472 More actions · Answer 8

sqlpanther (4/10/2013)
Yes all the objects are in the same server

Then you can do as Lowell suggests...or something similar with sys.columns if you need to drill down further.

demonfox SSCertifiable Points: 6289 More actions · Answer 9

Lowell (4/10/2013)
here's a really easy example:
--common tables between the two
SELECT name from SandBox.sys.tables
INTERSECT
SELECT name from Dictionary.sys.tables
--tables only in SandBox
SELECT name from SandBox.sys.tables
EXCEPT
SELECT name from Dictionary.sys.tables
--tables only in Dictionary
SELECT name from Dictionary.sys.tables
EXCEPT
SELECT name from SandBox.sys.tables

Apart from that , you need to check the database.sys.all_columns for attributes comparison....

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

sqlpanther Ten Centuries Points: 1204 More actions · Answer 10

Thanks SSchap...and all you ...it was quite quite simple...