如何使用sql查询数据库表结构的设计_sql文件怎么看数据库设计

1、sqlserver(注：表名必须大写)

select a.name 表名,b.name 字段名,c.name 字段类型,c.length 字段长度 from sysobjects
a,syscolumns b,systypes c where a.id=b.id and a.name=‘此处填写要查询的表名’ and
a.xtype=‘U’ and b.xtype=c.xtype

2、Oracle(注：表名必须大写)

1、查询数据库表的字段信息

select
A.column_name 字段名,A.data_type 数据类型,A.data_length 长度,A.data_precision 整数位,
A.Data_Scale 小数位,A.nullable 允许空值,A.Data_default 缺省值,B.comments 备注 from
user_tab_columns A,user_col_comments B where
A.Table_Name = B.Table_Name
and A.Column_Name = B.Column_Name
and A.Table_Name = ‘此处添加要查询的表名’

2、查询数据库表的索引

select
INDEX_NAME 索引名,INDEX_TYPE 索引类型,UNIQUENESS 索引类别 from
user_indexes where
TABLE_NAME = ‘此处添加要查询的表名’

3、查询数据库某个表的超全信息

select
A.column_name 字段名,A.data_type 数据类型,A.data_length 长度,A.data_precision 整数位,
A.Data_Scale 小数位,A.nullable 允许空值,A.Data_default 缺省值,B.comments 备注,
C.IndexCount 索引次数 from
user_tab_columns A,
user_col_comments B,
(select count(*) IndexCount,Column_Name from User_Ind_Columns where Table_Name = ‘此处添加要查询的表名’ group by Column_Name) C where
A.Table_Name = B.Table_Name
and A.Column_Name = B.Column_Name
and A.Column_Name = C.Column_Name(+)
and A.Table_Name = ‘此处添加要查询的表名’

4、查询Oracle数据库执行过的的sql记录

select * from v$sql  WHERE SQL_TEXT LIKE  '%%'
1

---

Oracle拓展：
– 创建数据表

create table TABLE_TEST (
NAME varchar2(40) not null,
SEX varchar2(1) default ‘’’‘Y’’’’ not null,
BIRTHDAY date not null,
HEIGHT number(3,2),
WEIGHT number(3,2),
MEMO blob );

– 给列添加备注

comment on column TABLE_TEST.NAME is ‘’’‘姓名’’’’; comment on column
TABLE_TEST.SEX is ‘’’‘性别’’’’; comment on column TABLE_TEST.BIRTHDAY
is ‘’’‘生日’’’’; comment on column TABLE_TEST.HEIGHT is ‘’’‘身高’’’’;
comment on column TABLE_TEST.WEIGHT is ‘’’‘体重’’’’; comment on column
TABLE_TEST.MEMO is ‘’’‘备注’’’’;

– 创建约束关系 主键 外键 其他

alter table TABLE_TEST add constraint TB_TEST_P_NAME primary key
(NAME);