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
