摘要：
1、 Oracle 1.获取当前Oracle数据库中所有选定的表_ namefrom_Tables2。查询字段名称、类型、精度、长度以及是否为空selectCOLUMN in a table _ name、DATA_ type、DATA_ precision、DATA_ SCALE、NULLABLE from user_ tab_ columnswhere table_ name='YourTableName'3
一、oracle
1、获取当前oracle数据库中的所有表
select table_name from user_tables
2、查询某个表中的字段名称、类型、精度、长度、是否为空
select COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE,NULLABLE from user_tab_columns where table_name ='YourTableName'
3、查询某个表中的主键字段名
select col.column_name from user_constraints con, user_cons_columns col where con.constraint_name = col.constraint_name and con.constraint_type='P' and col.table_name = 'YourTableName'
4、查询某个表中的外键字段名称、所引用表名、所应用字段名
select distinct(col.column_name),r.table_name,r.column_name from user_constraints con, user_cons_columns col, (select t2.table_name,t2.column_name,t1.r_constraint_name from user_constraints t1,user_cons_columns t2 where t1.r_constraint_name=t2.constraint_name and t1.table_name='YourTableName' ) r where con.constraint_name=col.constraint_name and con.r_constraint_name=r.r_constraint_name and con.table_name='YourTableName'
二、SQLServer
1、读取库中的所有表名
select name from sysobjects where xtype='u'
2、字段
SELECT c.name,t.name,c.xprec,c.xscale,c.isnullable FROM systypes t,syscolumns c WHERE t.xtype=c.xtype AND c.id = (SELECT id FROM sysobjects WHERE name='YourTableName') ORDER BY c.colid
3、主键（参考SqlServer系统存储过程sp_pkeys）
SELECT 表名=case when a.colorder=1 then d.name else '' end, 字段名=a.name, 标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√' else '' end, 主键=case when exists(SELECT 1 FROM sysobjects where xtype= 'PK' and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ))) then '√' else '' end, 类型=b.name, 占用字节数=a.length, 长度=COLUMNPROPERTY(a.id,a.name, 'PRECISION'), 小数位数=isnull(COLUMNPROPERTY(a.id,a.name, 'Scale'),0), 允许空=case when a.isnullable=1 then '√'else '' end, 默认值=isnull(e.text, ''), 字段说明=isnull(g.[value], '') FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype= 'U' and d.name <> 'dtproperties' --and d.name = '要查询的表名' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id and g.name='MS_Description' order by a.id,a.colorder
4、外键
select t1.name,t2.rtableName,t2.name from (select col.name, f.constid as temp from syscolumns col,sysforeignkeys f where f.fkeyid=col.id and f.fkey=col.colid and f.constid in ( select distinct(id) from sysobjects where OBJECT_NAME(parent_obj)='YourTableName' and xtype='F' ) ) as t1 , (select OBJECT_NAME(f.rkeyid) as rtableName,col.name, f.constid as temp from syscolumns col,sysforeignkeys f where f.rkeyid=col.id and f.rkey=col.colid and f.constid in ( select distinct(id) from sysobjects where OBJECT_NAME(parent_obj)='YourTableName' and xtype='F' ) ) as t2 where t1.temp=t2.temp
三、Access
1、所有表清单
conn.Open(); dt= conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
2、表结构
conn.Open(); dtColumnsInfo = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, strTableName,null });