oracle 查询表名、字段名、添加注释

时间:2022-03-01 13:45:23
--查询所有表名
SELECT T.TABLE_NAME FROM USER_TABLES T;

--查询所有字段名
SELECT T.COLUMN_NAME FROM USER_COL_COMMENTS T;

--查询指定表字段名和注释
SELECT T.COLUMN_NAME ,T.COMMENTS FROM USER_COL_COMMENTS T WHERE T.TABLE_NAME
= 'T_THREE_KILLED';

--查询表的数据条数、表名、中文表名
SELECT A.NUM_ROWS, A.TABLE_NAME, B.COMMENTS FROM USER_TABLES A, USER_TAB_COMMENTS B;
WHERE A.TABLE_NAME
= B.TABLE_NAME ORDER BY TABLE_NAME;

--给表添加注释
COMMENT ON TABLE T_THREE_KILLED IS
'三国杀人物表';

--给列添加注释
COMMENT ON COLUMN T_THREE_KILLED.C_SKILL IS
'技能';

--给列起别名 AS 和双引号可以去掉
SELECT T.C_NAME AS
"姓名",T.C_SKILL AS "技能" FROM T_THREE_KILLED T;

--去重
SELECT DISTINCT T.C_AGE FROM T_THREE_KILLED t;