Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I would like to list the default values for all columns in all tables in an Oracle database. The objective is to review the default values, and find any that need updating.
There are hundreds of tables in the database with columns that have default values, so it would be preferable to do this programmatically, rather than manually.
How can I do this?
Check user_tab_cols(all_tab_cols/dba_tab_cols). view . DATA_DEFAULT column, as its' name implies, holds default value for the column.
It turns out that the default values I use in my geodatabase are stored in proprietary XML system tables, not in regular Oracle table properties (ALL_TAB_COLS).
The default values can be queried from the XML system tables using a technique similar to what I outline here: GIS Stack Exchange - XML Table Query
