CODEWITHSUNDEEP
sqloracle-databaseloopsoracle11gmax
NightOwl Skeptic
NightOwl Skeptic

Reputation: 51

Get max(length(column)) for all columns in an Oracle table

I need to get the maximum length of data per each column in a bunch of tables. I'm okay with doing each table individually but I'm looking for a way to loop through all the columns in a table at least.

I'm currently using the below query to get max of each column-

select max(length(exampleColumnName)) 
from exampleSchema.exampleTableName;

I'm basically replacing the exampleColumnName with each column in a table. I've already went through 3-4 threads but none of them were working for me either because they weren't for Oracle or they had more details that I required (and I couldn't pick the part I needed).

I'd prefer to have it in SQL than in PLSQL as I don't have any create privileges and won't be able to create any PLSQL objects.

Upvotes: 1

Views: 14133

Answers (2)

David Faber
David Faber

Reputation: 12485

You can try this; although it uses PL/SQL it will work from within SQL-Plus. It doesn't loop. Hopefully you don't have so many columns that the SELECT query can't fit in 32,767 characters!

SET SERVEROUTPUT ON
DECLARE
    v_sql VARCHAR2(32767);
    v_result NUMBER;
BEGIN
    SELECT 'SELECT GREATEST(' || column_list || ') FROM ' || table_name
      INTO v_sql
      FROM (
        SELECT table_name, LISTAGG('MAX(LENGTH(' || column_name || '))', ',') WITHIN GROUP (ORDER BY NULL) AS column_list
          FROM all_tab_columns
         WHERE owner = 'EXAMPLE_SCHEMA'
           AND table_name = 'EXAMPLE_TABLE'
         GROUP BY table_name
    );
    EXECUTE IMMEDIATE v_sql INTO v_result;
    DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

Upvotes: 0

NightOwl Skeptic
NightOwl Skeptic

Reputation: 51

Got the below query to work - 

DECLARE
  max_length INTEGER; --Declare a variable to store max length in.
  v_owner VARCHAR2(255) :='exampleSchema';     -- Type the owner of the tables you are looking at

BEGIN
  -- loop through column names in all_tab_columns for a given table
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and table_name = 'exampleTableName') LOOP

    EXECUTE IMMEDIATE 
    -- store maximum length of each looped column in max_length variable
    'select nvl(max(length('||t.column_name||')),0) FROM '||t.table_name
    INTO max_length;

    IF max_length >= 0 THEN -- this isn't really necessary but just to ignore empty columns. nvl might work as well
      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||max_length ); --print the tableName, columnName and max length
    END IF;

  END LOOP;
END;

Do let me know if the comments explain it sufficiently, else I'll try to do better. Removing table_name = 'exampleTableName' might loop for all tables as well, but this is okay for me right now.

Upvotes: 4

Related Questions