Oracle SQL – Efficiently Write SELECT Queries for Tables with Many Columns

fieldsoracleselect

Due to limitations in my GIS software, I need to write select queries for each of my 200+ tables in my database. The queries need to select all columns, except for one, the SHAPE column.

Is there an efficient way to get all the field names for each table – for the purpose of writing select queries?

What I've tried:

  1. I think this is possible with SQL Developer(free). But unfortunately, I don't have SQL Developer, due to I.T. challenges.

  2. I'm aware that when I create a SELECT * view on a table in Oracle, the query is translated to explicitly select individual fields. So I could a) create a SELECT * view for each table, b) get the view definition, and c) write the query for each table. This is probably quicker than manually typing each field name, but not by a lot.

How can I efficiently write select queries for tables with lots of columns?

Best Answer

We can use the ALL_TAB_COLS view along with LISTAGG to generate the queries that you need. This strategy should work as long as your tables don't go above 125 columns or so. If you have too many very long column names LISTAGG won't return the full value because the return value is limited to a VARCHAR2(4000). Below is one implementation:

SELECT 'SELECT ' || COL_LIST || ' FROM SCOTT.' || TABLE_NAME || ';' QUERY_TO_RUN
FROM
(
  SELECT DISTINCT TABLE_NAME
  ,  LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID)  OVER (PARTITION BY TABLE_NAME) COL_LIST
  from ALL_TAB_COLS
  WHERE OWNER = 'SCOTT'
  -- AND TABLE_NAME IN (...)
  AND COLUMN_NAME <> 'SHAPE'
) t;

In that example I'm using the built-in SCOTT schema (not sure if this is present in your database). Here are the results for me:

SELECT ENAME, JOB, SAL, COMM FROM SCOTT.BONUS;
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM SCOTT.EMP;
SELECT GRADE, LOSAL, HISAL FROM SCOTT.SALGRADE;
SELECT DEPTNO, DNAME, LOC FROM SCOTT.DEPT;
Related Question