Comparison of database structure of novice learning sql,oracle

Reprinted from: http://www.cnblogs.com/tianqing/archive/2008/06/25/1229419.html

Oracle database structure comparison, comparison of specific details of the differences, such as: 2 libraries specific to those tables, different where, the primary key, length, type ..., the requirement is a SQL implementation, for me, this SQL rookie, is a challenge. Fortunately, there is a SQL Server example to learn from. First, post the SQL under Oracle

----------------------------------------------------------------------------------------Oracle Database Book comparison,--in this example, the Databasea database,          --Databaseb is a database. --in the actual operation, please modify this SQL as needed--------------------------------------------------------------------------------------Select( Case  whenA. Table Name 1 is NULL  andB. Serial number=1  Then 'Library 1 missing table:'||B. Table Name 2 whenB. Table Name 2 is NULL  andA. Serial number=1  Then 'Library 2 missing table:'||A. Table Name 1 whenA. Field name is NULL  and exists(Select 1  from(Selecttable_name asTable Name 1 fromDba_tab_columnswhere Upper(Dba_tab_columns.owner)=Upper('Databaseb'))whereTable Name 1=B. Table Name 2) Then 'Library 1 ['||B. Table Name 2||'] field is missing:'||B. Field name whenB. Field name is NULL  and exists(Select 1  from(Selecttable_name asTable Name 2 fromDba_tab_columnswhere Upper(Dba_tab_columns.owner)=Upper('Databasea'))whereTable Name 2=A. Table name 1) Then 'Library 2 ['||A. Table Name 1||'] field is missing:'||A. Field name whenA. Primary key<>B. Primary key Then 'primary key is different'                      whenA. Type<>B. Type Then 'Different Types'                      whenA. Field length<>B. Field length Then 'field length is different'                      whenA. Accuracy<>B. Accuracy Then 'different accuracy'                                          whenA. Decimal digits<>B. Decimal place Then 'different decimal digits'                      whenA. Allow null<>B. Allow null Then 'Allow empty different'                Else "' End) Result,a.*B.*       from(SelectA.table_name asTable Name 1, a.column_name asField name, B.constraint_type asPrimary KEY, A.data_type asType, column_id asSerial number, A.data_length asField length, a.data_precision asAccuracy, A.data_scale asdecimal place, a.nullable asAllow empty fromAll_tab_columns A Left Join(SelectD.constraint_type,e.column_name,e.table_name fromall_constraints d,all_cons_columns ewhereD.owner=E.owner andD.table_name=E.table_name andD.constraint_name=E.constraint_name andD.constraint_type= 'P') b onA.column_name=B.column_name andA.table_name=B.table_namewhereA.owner= Upper('Databaseb')Order  byTable Name 1ASC) A Full Join(SelectA.table_name asTable Name 2, A.column_name asField name, B.constraint_type asPrimary KEY, A.data_type asType, column_id asSerial number, A.data_length asField length, a.data_precision asAccuracy, A.data_scale asdecimal place, a.nullable asAllow empty fromAll_tab_columns A Left Join(SelectD.constraint_type,e.column_name,e.table_name fromall_constraints d,all_cons_columns ewhereD.owner=E.owner andD.table_name=E.table_name andD.constraint_name=E.constraint_name andD.constraint_type= 'P') b onA.column_name=B.column_name andA.table_name=B.table_namewhereA.owner= Upper('Databasea')Order  byTable Name 2ASC) b onA. Table Name 1=B. Table Name 2 andA. Field name=B. Field namewhereA. Table Name 1 is NULL orA. Field name is NULL orB. Table Name 2 is NULL orB. Field name is NULL  orA. Type<>B. TypeorA. Field length<>B. Field lengthorA. Accuracy<>B. AccuracyorA. Decimal digits<>B. Decimal placeorA. Allow null<>B. Allow nullorA. Primary key<>B. Primary keyOrder  byNVL (A. Table name 1,b. Table Name 2), NVL (A. Serial number, B. Serial number)

The execution results show the difference information in more detail. Can be based on this to modify the database, more convenient.
Now analyze this sentence of sq syntax.
1. Select the two database Oracle called all table structures under schema, i.e.:

All table StructuresSelectA.table_name asTable Name 1, a.column_name asField name, B.constraint_type asPrimary KEY, A.data_type asType, column_id asSerial number, A.data_length asField length, a.data_precision asAccuracy, A.data_scale asdecimal place, a.nullable asAllow empty fromAll_tab_columns A Left Join(SelectD.constraint_type,e.column_name,e.table_name fromall_constraints d,all_cons_columns ewhereD.owner=E.owner andD.table_name=E.table_name andD.constraint_name=E.constraint_name andD.constraint_type= 'P') b onA.column_name=B.column_name andA.table_name=B.table_namewhereA.owner= Upper('Databaseb')Order  byTable Name 1ASC

The above SQL allows you to select the table structure under a database by using the left JOIN to associate the column's primary key.
2. The table structure of the two libraries to be compared is associated with a full join.
3. Use case to compare specific different information. Then add the Where condition.

Summary, compare Oracle, 2 database table structure basically with the above SQL comparison, efficiency is slow, friends have good suggestions to talk about. Thank you.

Comparison of database structure of novice learning sql,oracle