Find all numeric columns in Oracle database

Numeric in SQL Server are columns with the following data types: NUMBER, BINARY_FLOAT, BINARY_DOUBLE.

The query below lists:

(A) all columns with numeric data types accessible to the current user in Oracle database

(B) all columns with numeric data types in Oracle database

Query

A. all columns with numeric data types accessible to the current user

select col.owner as schema_name,
       col.table_name,
       column_id,
       column_name,
       data_type,
       data_precision,
       data_scale
from sys.all_tab_cols col
join sys.all_tables tab on col.owner = tab.owner
                        and col.table_name = tab.table_name
where data_type in ('NUMBER','BINARY_FLOAT', 'BINARY_DOUBLE')
      and col.owner not in
      ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS','LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
      'OUTLN', 'WKSYS', 'APEX_040200')
order by col.owner,
         col.table_name,
         column_id;

B. all columns with numeric data types

select col.owner as schema_name,
       col.table_name,
       column_id,
       column_name,
       data_type,
       data_precision,
       data_scale
from sys.dba_tab_cols col
join sys.dba_tables tab on col.owner = tab.owner
                        and col.table_name = tab.table_name
where data_type in ('NUMBER','BINARY_FLOAT', 'BINARY_DOUBLE')
      and col.owner not in 
      ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS','LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
      'OUTLN', 'WKSYS', 'APEX_040200')
order by col.owner,
         col.table_name,
         column_id;

Columns

  • schema_name - name of the schema (owner)
  • table - name of the table
  • column_id - column position in table
  • column_name - name of the column
  • data_type - type of data
  • data_precision - decimal precision for NUMBER datatype; binary precision for FLOAT datatype
  • data_scale - digits to right of decimal point in a number

Rows

  • One row represents one column with a numeric data type
  • Scope of rows: all columns containing numeric data types in the database
  • Ordered by schema name and table name

Sample results

0
There are no comments. Click here to write the first comment.