How to Get All the Column Names in an Oracle Database
By Jason Gillikin
Oracle databases organize tables into owner accounts called schemas. Database users with varying privileges can query the database metadata -- called the "data dictionary" -- to list information including column names, object permissions or object statistics. To obtain column names from tables or views on an Oracle database server, run a short query using the most appropriate data-dictionary object. The USER_TAB_COLS view shows objects owned by the logged-in user, whereas ALL_TAB_COLS shows all objects available to the user given his permissions and DBA_TAB_COLS shows everything in the database irrespective of which user account owns the object.
Step 1
Execute a standard SQL query to return results from the most appropriate system view. A basic query appears in the form:
SELECT * FROM USER_TAB_COLS;
Substitute ALL_TAB_COLS or DBA_TAB_COLS as appropriate. The "*" symbol returns all columns in the query.
Step 2
Limit the data returned in the query by replacing "select *" with a more targeted list of columns from the dictionary view. To return only the schema, table name and column name for objects the logged-in account can access, use:
SELECT owner, table_name, column_name FROM ALL_TAB_COLS;
Step 3
Restrict which objects return by limiting your results with a "where" clause. For example, to return only column names for tables the logged-in user owns and that start with the letter "A," use:
SELECT * FROM USER_TAB_COLS WHERE table_name LIKE 'A%';
References
Tips
- Large organizations running an enterprise-grade Oracle server sometimes offer Web-based interfaces to data dictionaries that query all table columns as an anonymous or system user without having to write your own query. Check with a database administrator to see if you have access to such a tool.
- Oracle also offers ALL_TAB_COLUMNS, USER_TAB_COLUMNS and DBA_TAB_COLUMNS views. These objects differ from those ending in "COLS" by filtering out certain hidden columns. In most cases, you'll get the information you need regardless of which set of views you use.
Warnings
- Returning all column names on a very large database with many objects may provide too much information to be useful. Consider narrowing your scope of inquiry with a series of search filters in your SQL "where" clause.
- To query any data-dictionary object prefixed with DBA, the logged-in account must have "select" rights to those views or have Oracle's SELECT_CATALOG_ROLE assigned. In most cases, the DBA tables must specify the SYS schema prefix, e.g., "SELECT * FROM SYS.DBA_TAB_COLS;" or its equivalent.
Writer Bio
Jason Gillikin is a copy editor and writer who specializes in health care, finance and consumer technology. His various degrees in the liberal arts have helped him craft narratives within corporate white papers, novellas and even encyclopedias.