CODEWITHSUNDEEP
sqloracle-databasedatabase-administration
imtiyaz283
imtiyaz283

Reputation: 63

Oracle find table names having same structure

I have a database where people regularly create backup tables from existing table which i have to drop after sometime.Is there any query in oracle to find all tables having same structure but different names.

Upvotes: 1

Views: 800

Answers (3)

Patrick Bacon
Patrick Bacon

Reputation: 4660

Use aliases of all_tab_columns and join them to all_objects, making sure that the object type is of TABLE. Next, make sure the "matching" table is not itself (object_id are not equal to each other).

Next, count the matching columns with the total columns in each table and compare.

        SELECT t1.object_name table_name,
        col_ct.total_cols table_total_cols,
        t2.object_name matching_table,
        COUNT(1) matching_columns_ct
        FROM
        (SELECT o.object_id,
         COUNT(1) total_cols
         FROM all_objects o,
         all_tab_columns tc
         WHERE 1           =1
         AND o.object_type = 'TABLE'
         AND o.owner       = tc.owner
         AND o.object_name = tc.table_name
         GROUP BY o.object_id
         ) col_ct,
        all_objects t1,
        all_objects t2,
        all_tab_columns tc1,
        all_tab_columns tc2
        WHERE 1            =1
        AND t1.object_type = 'TABLE'
        AND t2.object_type = 'TABLE'
        AND t1.owner       =
        &TableOwner
        AND t1.object_name =
        &TableName
        AND t1.object_id    =col_ct.object_id
        AND t1.object_id   != t2.object_id
        AND t1.owner        = tc1.owner
        AND t1.object_name  = tc1.table_name
        AND t2.owner        = tc2.owner
        AND t2.object_name  = tc2.table_name
        AND tc1.column_name = tc2.column_name
        AND tc1.data_type   = tc2.data_type
        GROUP BY t1.object_name,
        col_ct.total_cols,
        t2.object_name
        HAVING COUNT(1) =col_ct.total_cols

Upvotes: 0

KevinKirkpatrick
KevinKirkpatrick

Reputation: 1456

Below query first finds all tables with matching # columns and 32-bit hash of column position+name+data_type. Birthday paradox comes into play here - there's an outside chance of accidental collisions (approaching 50% chance of 1 false positive in a database with 65,000 tables). If you're filtering for user schemas and searching through significantly less than 65,000 total tables, just query T0 directly. Otherwise, remaining SQL will ensure you only get tables with matching column structures.

WITH T AS 
  (
    SELECT  OWNER O, 
            TABLE_NAME N, 
            SUM(ORA_HASH(COLUMN_ID||COLUMN_NAME||DATA_TYPE,POWER(2,32)-1)) H, 
            COUNT(*) C
    FROM    DBA_TABLES 
            JOIN  DBA_TAB_COLUMNS USING (OWNER,TABLE_NAME)
    -----------------------------------------------------------------
    -- where clause here to limit schemas to user-defined tables
    -----------------------------------------------------------------
    GROUP BY OWNER, TABLE_NAME
  )
SELECT  O1, N1, O2, N2 
FROM 
        (
            SELECT  T1.N N1, T1.O O1, T2.N N2, T2.O O2, C 
            FROM    T T1 JOIN T T2 USING (H, C)
            WHERE   (T1.N <> T2.N OR T1.O <> T2.O)
        ) T0
---------------------------------------------------------------------
-- Remaining SQL only needed if false-positives must be avoided
---------------------------------------------------------------------
        JOIN DBA_TAB_COLUMNS T1 ON (T0.O1 = T1.OWNER AND T0.N1=T1.TABLE_NAME)
        JOIN DBA_TAB_COLUMNS T2 ON (T0.O2 = T2.OWNER AND T0.N2 = T2.TABLE_NAME
                                    AND T2.COLUMN_ID = T1.COLUMN_ID
                                    AND T2.COLUMN_NAME = T1.COLUMN_NAME
                                    AND T2.DATA_TYPE = T1.DATA_TYPE)
GROUP BY O1, N1, O2, N2, C
HAVING COUNT(*) = C

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you have a particular table, try this to get the count of tables with common column names:

select atc.owner, atc.table_name, count(*) as ColumnsInCommon
from all_tab_columns atc join
     all_tab_columns atc1
     on atc.column_name = atc1.column_name and
        atc1.table_name = YOURTABLENAME and
        atc1.owner = YOUROWNER
group by atc.owner, atc.table_name
order by count(*) desc;

This should show your original table on top.

Upvotes: 0

Related Questions