- 创建表,并给表和字段加上描述
CREATE TABLE MANTIS_CODE(
ID VARCHAR2(32) NOT NULL,
NAME VARCHAR2(255) NOT NULL
);
CREATE TABLE MANTIS_CODE_ITEM(
CODE_ID VARCHAR2(32) NOT NULL,
VALUE VARCHAR2(32) NOT NULL,
NAME VARCHAR2(255)
)
;
- .创建主键
alter TABLE MANTIS_CODE add constraint PK_MANTIS_CODE primary key (ID)
- 联合主键
alter TABLE MANTIS_CODE add constraint PK_MANTIS_CODE primary key (ID,NAME)
- 创建表描述
COMMENT ON TABLE mantis_code is '代码表'
- 创建字段描述
COMMENT ON COLUMN mantis_code.ID is '代码标识'
- 创建外键
alter table MANTIS_CODE_ITEM add constraint FK_MANTIS_CODE_ITEM foreign key(CODE_ID) references MANTIS_CODE(ID)
- 取得表的描述
select * from user_tab_comments where comments is not null
- 取得字段
select t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE,t.DATA_LENGTH,t.DATA_PRECISION,t.DATA_SCALE,
t.NULLABLE,t.DATA_DEFAULT from user_tab_columns t where t.TABLE_NAME='USER_INFO'
- 取得字段的描述
select * from user_col_comments where comments is not null;
- 取得表的主键
select * from user_cons_columns cc,user_constraints c
where c.constraint_name = cc.constraint_name
and c.constraint_type = 'P'
and cc.table_name = 'USER_INFO'
- 取得外键
select r.CONSTRAINT_NAME,
r.TABLE_NAME,
r.R_CONSTRAINT_NAME,
rc.COLUMN_NAME,
pc.COLUMN_NAME as PK_COLUMN_NAME,
pc.TABLE_NAME as PK_TABLE_NAME
from USER_CONSTRAINTS r,
USER_CONS_COLUMNS rc,
USER_CONSTRAINTS p,
USER_CONS_COLUMNS pc
where r.CONSTRAINT_TYPE = 'R'
and r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
and pc.CONSTRAINT_NAME = p.CONSTRAINT_NAME
and p.CONSTRAINT_TYPE = 'P'
and r.R_CONSTRAINT_NAME = p.CONSTRAINT_NAME
and r.TABLE_NAME = 'USER_ROLE_MAP'
如果取得数据库表则从ALL_CONSTRAINTS ALL_CONS_COLUMNS中取得
你可以通过这个链接引用该篇文章:http://lianghyan.bokee.com/tb.b?diaryId=18763148