ALL_TAB_COLUMNS describes the columns of the tables, views, and clusters accessible to the current user.
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)); |