General discussion


How can I find out differcences from two database?

By murtuja_oracle ·
I have two databases.Both are same.Now I want to
compare both databse by each and every objects like
How can I find out differcences from two database?

Can u provide me script for that? or any free tool for that?


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Database Differences

by Catadmin In reply to How can I find out differ ...

The best and easiest way of looking at the differences is creating a datadictionary which lists out your tables, columns and objects. Then you can compare the data dictionary reports side-by-side. I recently did one with Reporting Services 2000 (which isn't the best tool since it's a first iteration program, but is free). RS 2k5 promises to be better.

However, a quick and dirty (but potentially confusing) way of doing it is to use already existing system Stored Procedures and system tables for finding your details.

Look in Books Online for the Information_Schema.<name> views that pull base information about your table names and column names from the system tables. Information_Schema.Columns, Information_Schema.Tables and Information_Schema.Table_Constraints are just three of many system views that will give you information on your objects. Additionally, sp_addExtendedProperty and fn_ListExtendedProperty are great for adding definitions to database objects or viewing previously created object definitions.

SysTypes, SysObjects, SysColumns all contain the details of the various tables/columns including datatypes, column defaults, etc. But be very leary of doing select statements directly against system tables as doing them wrong can alter your system tables and cause your entire SQL Server to crash (worst case scenario). Mostly, you'll want to use the Information_Schema views to avoid damage.

Other than that, you could generate the complete script for each database and do a line-by-line compare of the objects after the scripts are generated.

Purchasable tools that do database compares include Computer Associates "Erwin" data modeling program which can do a complete compare between two databases and is much better than doing a manual compare. It also enables you to reverse engineer a database for printing out a db diagram and creating brand new models for soon-to-be databases.

Hope that helps!


Collapse -


by entawanabi In reply to How can I find out differ ...

If you have a Search Feature in your Dbase system do the followwing: Post to ________ file all non- two-point datums with address, from database(address) and database(address) by COMPARE EACH.

Ithink that is correct or at least close enough that you can reword the search to accomplish what you need.

Collapse -


by Catadmin In reply to Maybe?

I just realized I assumed murtuja was using a SQL Server database instead of an Oracle or whatever. My bad.

Off the top of my head, though, I don't know of any such functionality in SQL Server 2000 (which doesn't mean there isn't any, I just haven't heard of it yet). What database system do you use that search function in? And if it is SQL Server, where can I find more information about it?



Collapse -


by entawanabi In reply to Opps

Of course you would ask me something I don't know anthing about; what's a 'SQL server'?

Supposedly either your data base provdes for a search mechanism or the provider 'ware does/ example of a providerware is Excell or Dbase or ACCESS. If not your Comptroller, your senior data base manager or the head of personell can or is 'sposed to be able to assist you with a legal to your system import. Also YOU may not be of sufficient position 'OFFICIALLY' per your uninformed Data base; finally you could ask your security manager to see were you stand in making such a search!

Related Discussions

Related Forums