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
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 = '表名'