oracle查询表结构语法,Oracle查询表结构语句，

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

http://www.htsjk.com/teradata/33935.html

www.htsjk.Com

true

http://www.htsjk.com/teradata/33935.html

NewsArticle

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'...

本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.

同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。

相关文章

暂无相关文章