Efficiently write SELECT queries for tables with lots of columns (not SELECT *)

Question

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?

Answer 1

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;

Answer 2

For the record, this is how I implemented Joe Obbish's answer in an ESRI SDE.ST_GEOMETRY Oracle database:

SELECT
    a.REGISTRATION_ID 
    ,a.TABLE_NAME
    ,b.QUERY_NO_SHAPE
    ,LENGTH(b.QUERY_NO_SHAPE) AS QUERY_LENGTH
FROM 
    SDE.TABLE_REGISTRY a
LEFT JOIN 
    (
        SELECT TABLE_NAME, 'SELECT ' || COL_LIST || ' FROM ENG.' || TABLE_NAME || ';' QUERY_NO_SHAPE
        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 = 'ENG'
            AND COLUMN_NAME NOT LIKE 'SYS_%'
            AND COLUMN_NAME <> 'SHAPE'
        )
    ) b
    ON a.TABLE_NAME = b.TABLE_NAME
WHERE 
    a.OWNER='ENG'

There are a bunch of system tables in the database that I don't need to generate queries for. So I join from SDE.TABLE_REGISTRY to Joe's query so that I only generate queries for real tables. Additionally, there are system columns within the tables that are eliminated from the query.

The longest query length is 900 characters, so the query generates/runs successfully.