Oracle:
查詢某個表中的字段名稱類型精度長度是否為空
select COLUMN_NAMEDATA_TYPEDATA_PRECISIONDATA_SCALENULLABLE
from user_tab_columns
where table_name =YourTableName
查詢某個表中的主鍵字段名
select lumn_name
from user_constraints con user_cons_columns col
where nstraint_name = nstraint_name
and nstraint_type=P
and coltable_name = YourTableName
查詢某個表中的外鍵字段名稱所引用表名所應用字段名
select distinct(lumn_name)rtable_namelumn_name
from
user_constraints con
user_cons_columns col
(select ttable_namelumn_nametr_constraint_name
from user_constraints tuser_cons_columns t
where tr_constraint_name=nstraint_name
and ttable_name=YourTableName
) r
where nstraint_name=nstraint_name
and conr_constraint_name=rr_constraint_name
and contable_name=YourTableName
SQLServer中的實現
字段
SELECT cnametnamecxpreccxscalecisnullable
FROM systypes tsyscolumns c
WHERE txtype=cxtype
AND cid = (SELECT id FROM sysobjects WHERE name=YourTableName)
ORDER BY lid
主鍵（參考SqlServer系統存儲過程sp_pkeys）
select COLUMN_NAME = convert(sysnamecname)
from
sysindexes i syscolumns c sysobjects o
where oid = object_id([YourTableName])
and oid = cid
and oid = iid
and (istatus & x) = x
and (cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid ) or
cname = index_col ([YourTableName] iindid )
)
外鍵
select tnametrtableNametname
from
(select colname nstid as temp
from syscolumns colsysforeignkeys f
where ffkeyid=colid
and ffkey=lid
and nstid in
( select distinct(id)
from sysobjects
where OBJECT_NAME(parent_obj)=YourTableName
and xtype=F
)
) as t
(select OBJECT_NAME(frkeyid) as rtableNamecolname nstid as temp
from syscolumns colsysforeignkeys f
where frkeyid=colid
and frkey=lid
and nstid in
( select distinct(id)
from sysobjects
where OBJECT_NAME(parent_obj)=YourTableName
and xtype=F
)
) as t
where ttemp=ttemp
From:http://tw.wingwit.com/Article/program/Oracle/201311/18610.html
