How to compare objects in two databases in sql 2k...

[copernicus]

Hi,


I want to compare the production (say prod_db) objects against development database say dev_db.

In Oracle I would run a query using "minus" that is

select table_name from user_tables@prod_db
minus
select table_name from
user_tables@dev_db

what is the equivalent sql in t-sql(or are there any GUI tools to do this).I understand that from prod_db I need to create a link server to the dev_db to run such a query.

Thanks,
copernicus.

[MAK]

This has already been answered in this thread.


http://forums.databasejournal.com/sh...ghlight=oracle

[copernicus]

Thanks for replying...
I am kind of new to sql server 2k..
I may not have put the question properly....I want to do this...

In reality I have to compare two databases in sql server 2k.Let us say the Source database is prod and the target database is say dev.I want to compare all objects in source vs target(that is tables,indexes,constraints,views,triggers etc..)

that is want to know missing objects in target.

p.s : I know that there are 3rd party tools like db diff but would not to use these as I want to learn how to do this....

Thanks,
Copernicus.

[MAK]

read the complete thread....

http://forums.databasejournal.com/sh...hlight=compare

[copernicus]

Thanks Mak,

I think I understand your answer.

Let me put the question in a diffrent way.

How do I compare the prod_db database tables against dev_db tables (just names ,no data comparison)? What would be that query considering that dev_db
is on a diffrent server and say there is a link from prod_db to dev_db with the name "link2dev_db".


Thanks,
copernicus.

[MAK]

--compare dev to prod

select name from sysobjects where name not in select name from link2dev_db.database.dbo.sysobjects


--compare prod to dev
select name from link2dev_db.database.dbo.sysobjects
where name not in select name from sysobjects

[copernicus]

Thanks ...