查询oracle表字段名称

SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH
FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='T_CAKEY'

 

SELECT * FROM USER_TABLES --查询当前用户所拥有的表
SELECT * FROM DBA_TABLES --拥有DBA权限用户能查询所有的表
SELECT * FROM ALL_TABLES --查询当前用户能访问的表

查询表字段明细

SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION ,DATA_SCALE

FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='TABLENAME'

字段注释

SELECT   *   FROM   USER_COL_COMMENTS;

表注释

SELECT   *   FROM   USER_TAB_COMMENTS;

表明细+表注释+字段明细+字段注释

SELECT ATC.OWNER,ATC.TABLE_NAME,UTC.COMMENTS,ATC.COLUMN_NAME,ATC.DATA_TYPE,ATC.DATA_LENGTH,ATC.NULLABLE, UCC.COMMENTS FROM

(SELECT ATC.OWNER,ATC.TABLE_NAME,ATC.COLUMN_NAME,ATC.DATA_TYPE,ATC.DATA_LENGTH,ATC.NULLABLE

FROM ALL_TAB_COLUMNS ATC WHERE ATC.OWNER IN (用戶名1，用戶名2........) ) ATC

LEFT OUTER JOIN USER_COL_COMMENTS UCC ON ATC.TABLE_NAME=UCC.TABLE_NAME AND ATC.COLUMN_NAME=UCC.COLUMN_NAME

LEFT OUTER JOIN USER_TAB_COMMENTS UTC ON ATC.TABLE_NAME=UTC.TABLE_NAME

ORDER BY ATC.TABLE_NAME,ATC.COLUMN_NAME;

通过表名 查询表 字段信息插入到 A表

INISET INTO    A表(字段名称,字段注释,字段类型,字段长度)
           SELECT    C1.COLUMN_NAME ,C2.COMMENTS , C1.DATA_TYPE , C1.DATA_LENGTH
                FROM ALL_TAB_COLUMNS  C1
                LEFT JOIN USER_COL_COMMENTS  C2 ON C1.TABLE_NAME=C2.TABLE_NAME AND C1.COLUMN_NAME=C2.COLUMN_NAME
                       WHERE C1.TABLE_NAME = '表名'