Stack Exchange network consists of 183 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
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.
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
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