Oracle查询表结构语句

Oracle中使用SQL查询表结构

SELECT   NULL AS table_cat, t.owner AS table_schem,
         t.table_name AS table_name, t.column_name AS column_name,
         DECODE (t.data_type,
                 'CHAR', 1,
                 'VARCHAR2', 12,
                 'NUMBER', 3,
                 'LONG', -1,
                 'DATE', 91,
                 'RAW', -3,
                 'LONG RAW', -4,
                 'BLOB', 2004,
                 'CLOB', 2005,
                 'BFILE', -13,
                 'FLOAT', 6,
                 'TIMESTAMP(6)', 93,
                 'TIMESTAMP(6) WITH TIME ZONE', -101,
                 'TIMESTAMP(6) WITH LOCAL TIME ZONE', -102,
                 'INTERVAL YEAR(2) TO MONTH', -103,
                 'INTERVAL DAY(2) TO SECOND(6)', -104,
                 'BINARY_FLOAT', 100,
                 'BINARY_DOUBLE', 101,
                 1111
                ) AS data_type,
         t.data_type AS type_name,
         DECODE (t.data_precision,
                 NULL, t.data_length,
                 t.data_precision
                ) AS column_size,
         0 AS buffer_length, t.data_scale AS decimal_digits,
         10 AS num_prec_radix, DECODE (t.nullable, 'N', 0, 1) AS nullable,
         NULL AS remarks, t.data_default AS column_def, 0 AS sql_data_type,
         0 AS sql_datetime_sub, t.data_length AS char_octet_length,
         t.column_id AS ordinal_position,
         DECODE (t.nullable, 'N', 'NO', 'YES') AS is_nullable
    FROM all_tab_columns t
   WHERE t.owner LIKE 'ODS_USER' ESCAPE '/'
     AND t.table_name LIKE 'ODS_PRPCMAIN' ESCAPE '/'
  --   AND t.column_name LIKE :3 ESCAPE '/'
ORDER BY table_schem, table_name, ordinal_position

 

如果需要DDL可如下查询：

1.SELECT DBMS_METADATA.get_ddl ('TABLE', 'ODS_PRPCMAIN_COEFF', 'ODS_USER')   
2.FROM DUAL