Richlab Team

Example 1:

SELECT column_name, data_type FROM all_tab_columns

Example 2:

CREATE OR REPLACE VIEW V_LOG_SCHEMA AS
SELECT object_name,
       column_name,
       column_type,
       object_text,
       created,
       lastupdate,
       comments
FROM
-- [T]able, [V]iew
(SELECT CASE WHEN object_name IS NULL THEN '' ELSE '[' 
                                                    || SUBSTR(object_type, 0, 1) 
                                                    || '] ' 
                                                    || object_name END object_name,
       tab.column_name,
       tab.column_type,
       tab.object_text,
       tab.created,
       tab.lastupdate,
       tab.comments
  FROM (SELECT usr_com.table_name object_name,
               usr_com.table_type object_type,
               '' column_name,
               0 column_id,
               '' column_type,
               usr_com.comments,
               dbms_metadata.get_ddl(object_type,object_name,'SIGO') object_text,
               to_char(usr_obj.CREATED, 'MM/DD/YYYY HH24:MI:SS') created,
               to_char(usr_obj.LAST_DDL_TIME, 'MM/DD/YYYY HH24:MI:SS') lastupdate,
               usr_obj.OBJECT_TYPE || usr_com.table_name || 1 mtlevel
          FROM USER_TAB_COMMENTS usr_com
         INNER JOIN USER_OBJECTS usr_obj
            ON usr_com.TABLE_NAME = usr_obj.OBJECT_NAME
        UNION ALL
        SELECT '' object_name,
               '' object_type,
               usr_columns.column_name,
               usr_columns.column_id,
               CONCAT(CONCAT(CONCAT(usr_columns.data_type, '('), 
                                    usr_columns.data_length),
                      ')') || ' [' || nullable || ']' column_type,
               usr_colcom.comments,
               NULL object_text,
               '' created,
               '' lastupdate,
               usr_obj.OBJECT_TYPE || usr_columns.table_name || 2 mtlevel
          FROM USER_TAB_COLUMNS usr_columns
         INNER JOIN USER_COL_COMMENTS usr_colcom
            ON usr_columns.COLUMN_NAME = usr_colcom.column_name
           AND usr_columns.TABLE_NAME = usr_colcom.table_name
         INNER JOIN USER_OBJECTS usr_obj 
            ON usr_columns.TABLE_NAME = usr_obj.OBJECT_NAME
           ) tab
 ORDER BY mtlevel, column_id)
 UNION ALL
 -- [P]rocedure, [S]equence, [F]unction
 (SELECT object_name,
       column_name,
       column_type,
       object_text,
       created,
       lastupdate,
       comments
  FROM (SELECT '[' || substr(object_type, 0, 1) || '] ' || object_name object_name,
               '' column_name,
               '' column_type,
               '' comments,
               dbms_metadata.get_ddl(object_type, object_name, 'SIGO') object_text,
               to_char(CREATED, 'MM/DD/YYYY HH24:MI:SS') created,
               to_char(LAST_DDL_TIME, 'MM/DD/YYYY HH24:MI:SS') lastupdate,
               object_type || object_name mtlevel
          FROM USER_OBJECTS
         WHERE OBJECT_TYPE IN ('PROCEDURE', 'SEQUENCE', 'FUNCTION')
         ORDER BY mtlevel));