General
cancel
Showing results for 
Search instead for 
Did you mean: 

list table and fields and compare to another table fields

SOLVED
Go to solution
Ratzie
Super Advisor

list table and fields and compare to another table fields

Here is what I want to do.
we are upgrading a database, and I need to determine if any thing has changed in the tables.

So how do I take a dump of tables from a schema, then do a compare and see if the columns are like for like between now and upgraded schema?

I want to do this in ksh with sql.
6 REPLIES
Michael Steele_2
Honored Contributor

Re: list table and fields and compare to another table fields

Hi

I would export it to Word or Excel and use the Search and Sort utilities from these applications.

Else I'll need more information to write a script. Please attach a sample file and what you want to check.
Support Fatherhood - Stop Family Law
Ratzie
Super Advisor

Re: list table and fields and compare to another table fields

hmmm.
Lets break it down to two smaller tasks.

First list all tables in schema.

I would do this for both current and new.
Then do a compare of the two, to see what is new.
How do I pull the table names from a schema to a flat file.

Next, what I want to do, is pull the table name for a schema with all the columns and compare to new, to see if any additional columns were added.

Would I use describe?

hope this helps. Oracle database.
Jean-Luc Oudart
Honored Contributor
Solution

Re: list table and fields and compare to another table fields

Ratzie

there are scripts around to do what you want (as I understand). wrote the wrapper fro the list of tables to compare.

http://dbathoughts.blogspot.com/2008/02/compare-table-structure-in-oracle.html

Regards
Jean-Luc
fiat lux
Ratzie
Super Advisor

Re: list table and fields and compare to another table fields

This is exactly what I need but with one caveat!

I have the table that I want to compare on another server/database.

How do I set up that connection?
Jean-Luc Oudart
Honored Contributor

Re: list table and fields and compare to another table fields

One option is to import the schema (shema only , no rows) to a different schema and compare table shema1.a with table shema2.b definition.

impoting the schema (descriptions) will work perfect for dedicated set of tables. As no rows are imported little space is required.


Regards
Jean-Luc
fiat lux
Ratzie
Super Advisor

Re: list table and fields and compare to another table fields

Thank you for all the work. I have found the TOAD actually has a compare schema's and provided my all that I needed.