Thursday, January 12, 2012

Find duplicate columns from all tables in a schema


The below script is used to find the same columns appearing in multiple tables of a particular schema


SELECT column_name,data_type,data_length,nullable,table_name
FROM dba_tab_columns
WHERE column_name IN
(
    SELECT column_name
    FROM dba_tab_columns
    GROUP BY
        column_name
    HAVING COUNT(1) > 1  -- more than one value
)
and owner = 'USER' -- provide the user name
AND COLUMN_NAME LIKE '%TIME%'
ORDER BY column_name

No comments:

Post a Comment