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

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

2009-02-19 08:52:10|??分类： Oracle |字号?订阅

取得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?? on?? a.id=b.parent_obj??
? join?? sysforeignkeys?? c?? on?? b.id=c.constid??
? join?? sysobjects?? d?? on?? c.rkeyid=d.id??
? join?? sysindexkeys?? e?? on?? d.id=e.id??
? join?? syscolumns?? f?? on?? a.id=f.id?? and?? e.colid=f.colid??
? where???? b.xtype='F'

?

取得Oracle数据库的所有外键信息：

?

select all_constraints.constraint_name as key_name,?? all_constraints.table_name as name,?? all_indexes.table_name as f_name from all_constraints,all_indexes where? constraint_name like 'FK%' and all_constraints.r_constraint_name = all_indexes.index_name

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!