How to Get All the Column Names in an Oracle Database

By Jason Gillikin

Larry Ellison, leader of Oracle, Inc., shares product updates at trade shows.
i Justin Sullivan/Getty Images News/Getty Images

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:


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: