mysql外键:Ms Sqlserver与Oralce取得表的列信息与外键信息

on 2009-2-12 in 数据库 | 0 Comment


取得Ms SqlServer数据库中某张表所有列:

SELECT sysobjects.name AS TABLENAME,
syscolumns.NAME as NAME,
systypes.name VTYPE,
syscolumns.LENGTH,
syscolumns.XPREC,
syscolumns.XSCALE,
isnullable,
cdefault
FROM syscolumns,sysobjects,systypes
WHERE syscolumns.id = sysobjects.id and
syscolumns.xtype = systypes.xtype and
sysobjects.type='U' and
sysobjects.name = 表名

取得Oracle数据库某张表所有列:

SELECT TNAME AS TABLENAME,
CNAME AS NAME,
COLTYPE AS VTYPE,
WIDTH AS LENGTH,
PRECISION AS XPREC,
SCALE AS XSCALE,
NULLS AS isnullable,
DEFAULTVAL AS cdefault
FROM COL
WHERE TNAME = 表名;

取得Ms Sqlserver数据库所有外键信息

select
b.name as key_name,
a.name as name,
d.name as f_name

from sysobjects a join sysobjects b _disibledevent=>join sysforeignkeys c _disibledevent=>join sysobjects d _disibledevent=>join sysindexkeys e _disibledevent=>join syscolumns f _disibledevent=>where b.xtype='F'

取得Oracle数据库所有外键信息:
select all_constras.constra_name as key_name,
all_constras.table_name as name ,
all_indexes.table_name as f_name
from all_constras,all_indexes
where constra_name like 'FK%'
and all_constras.r_constra_name = all_indexes.index_name
Tags:  外键约束 主键外键 sql外键 mysql外键

延伸阅读

最新评论

发表评论