Oracle and PL/SQL

Oracle: Finding Columns with only null values

automatedcode

Consider we are have a table name mytable, which has a lot of  columns and many of them have null values. We can get information about the table using the Oracle built-in namespace.

USER_TAB_COLUMNS

USER_TAB_COLUMNS describes the columns of the tables, views, and clusters owned by the current user. Its columns (except for OWNER) are the same as those in ALL_TAB_COLUMNS. Columns

  • TABLE_NAME -Table,view or cluster name.
  • COLUMN_NAME – Column name.
  • DATA_TYPE -Datatype of the column.
  • DATA_TYPE_MOD – Datatype modifier of the column.
  • DATA_TYPE_OWNER – Owner of the datatype of the column.
  • DATA_LENGTH – Length of the column in bytes.
  • DATA_PRECISION – Length: decimal digits (NUMBER) or binary digits (FLOAT).
  • DATA_SCALE – Digits to right of decimal point in a number.
  • NULLABLE – Does column allow NULL values?
  • COLUMN_ID – Sequence number of the column as created.
  • DEFAULT_LENGTH – Length of default value for the column.
  • DATA_DEFAULT – Default value for the column.
  • NUM_DISTINCT – The number of distinct values in the column.
  • LOW_VALUE – The low value in the column.
  • HIGH_VALUE – The high value in the column.
  • DENSITY – The density of the column.
  • NUM_NULLS – The number of nulls in the column.
  • NUM_BUCKETS – The number of buckets in histogram for the column.
  • LAST_ANALYZED – The date of the most recent time this column was analyzed.
  • SAMPLE_SIZE – The sample size used in analyzing this column.
  • CHARACTER_SET_NAME – Character set name.
  • CHAR_COL_DECL_LENGTH – Declaration length of character type column.
  • GLOBAL_STATS – Are the statistics calculated without merging underlying partitions?
  • USER_STATS – Were the statistics entered directly by the user?
  • AVG_COL_LEN – The average length of the column in bytes.
  • CHAR_LENGTH – The maximum length of the column in characters.
  • CHAR_USED – C is maximum length given in characters,B if in bytes.
  • V80_FMT_IMAGE – Is column data in 8.0 image format?
  • DATA_UPGRADED – Has column data been upgraded to the latest type version format?
  • HIDDEN_COLUMN – The column exists in the table but cannot be selected. (mostly for internal use)
  • VIRTUAL_COLUMN – The column is an Oracle 11g Virtual column.
SELECT t.column_name FROM user_tab_columns t WHERE t.nullable = 'Y' AND t.table_name = 'YOUR_TABLE_NAME' AND t.num_distinct = 0

Leave a comment