CODEWITHSUNDEEP
sqloracle-database
shaqino
shaqino

Reputation: 23

Extract table_name, column_name, data_type, data_length and example of value in Oracle DB

I'm facing a strange request at my job regarding the extraction of the structure of a database. In this extraction the requirer needs the table name, the column name, the column type and the maximum length for VARCHAR columns. In addition it was asked if there is the possibility to provide also an example of value.

This is my query, at the moment

SELECT 
    table_name, 
    column_name, 
    data_type,
    CASE WHEN (data_type = 'VARCHAR' OR data_type = 'VARCHAR2') THEN TO_CHAR(char_length) ELSE '-' END AS char_length 
FROM all_tab_cols 
WHERE owner = 'DB_OWNER' 
ORDER BY table_name;

Is there a chance to make a similar query adding also an example of a value contained in each column?

Personally I think it is not possible unless using a very complex (and heavy) stored procedure, but probably someone more skilled than me in database administration has a better input.

Thanks in advance

Upvotes: 0

Views: 641

Answers (1)

Littlefoot
Littlefoot

Reputation: 142743

Dynamic SQL is what you need. For example:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     l_val  VARCHAR2 (20);
  3  BEGIN
  4     FOR cur_r IN (  SELECT table_name,
  5                            column_name,
  6                            data_type,
  7                            CASE
  8                               WHEN (   data_type = 'VARCHAR'
  9                                     OR data_type = 'VARCHAR2')
 10                               THEN
 11                                  TO_CHAR (char_length)
 12                               ELSE
 13                                  '-'
 14                            END AS char_length
 15                       FROM all_tab_cols
 16                      WHERE     owner = 'SCOTT'
 17                            AND table_name IN ('DEPT', 'EMP')
 18                   ORDER BY table_name)
 19     LOOP
 20        EXECUTE IMMEDIATE   'select max('
 21                         || cur_r.column_name
 22                         || ') from '
 23                         || cur_r.table_name
 24           INTO l_val;
 25
 26        DBMS_OUTPUT.put_line (
 27              RPAD (cur_r.table_name || '.' || cur_r.column_name, 15, ' ')
 28           || ': '
 29           || l_val);
 30     END LOOP;
 31  END;
 32  /
DEPT.DEPTNO    : 40
DEPT.LOC       : NEW YORK
DEPT.DNAME     : test
EMP.MGR        : 7902
EMP.HIREDATE   : 12.01.1983 00:00
EMP.SAL        : 5000
EMP.COMM       : 1400
EMP.DEPTNO     : 30
EMP.ENAME      : WARD
EMP.EMPNO      : 7934
EMP.JOB        : SALESMAN

PL/SQL procedure successfully completed.

SQL>

Upvotes: 2

Related Questions