Phil's Notes

Tagged “sql”

  1. Finding the Unique Column in an Oracle Table

    2. sql pl/sql oracle

    If you ever find yourself looking for the unique column of a table in an Oracle database, but don’t see a primary key assigned and you don’t want to run a query against all the records in the table because it is too large, you can try querying against DBA_TABLES or ALL_TABLES to get the number of rows in that table.

    SELECT NUM_ROWS
    FROM DBA_TABLES --you can also use ALL_TABLES
    WHERE TABLE_NAME = 'your_table_name';

    Once you have the number of rows, query DBA_TAB_COLUMNS or ALL_TAB_COLUMNS to see which column has the same distinct number of rows (NUM_DISTINCT) as the NUM_ROWS from the previous query.

    SELECT COLUMN_NAME, NUM_DISTINCT
    FROM DBA_TAB_COLUMNS --you can also use ALL_TAB_COLUMNS
    WHERE TABLE_NAME = 'your_table_name'
    ORDER BY NUM_DISTINCT DESC;

    Source: https://stackoverflow.com/a/4010826/991383

See all tags.