关于Oracle与SqlServer中获取所有字段、主键、外键的sql语句

xr1128

--oracle:--查询某个表中的字段名称、类型、精度、长度、是否为空select COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE,NULLABLE from user_tab_columns where table_name ='YourTableName'--查询某个表中的主键字段名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'--查询某个表中的外键字段名称、所引用表名、所应用字段名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中的实现：--字段：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--主键（参考SqlServer系统存储过程sp_pkeys）：select COLUMN_NAME = convert(sysname,c.name)               from                                                       sysindexes i, syscolumns c, sysobjects o                   where o.id = object_id('[YourTableName]')                  and o.id = c.id                                            and o.id = i.id                                            and (i.status & 0x800) = 0x800                             and (c.name = index_col ('[YourTableName]', i.indid,  1) or          c.name = index_col ('[YourTableName]', i.indid,  2) or          c.name = index_col ('[YourTableName]', i.indid,  3) or          c.name = index_col ('[YourTableName]', i.indid,  4) or          c.name = index_col ('[YourTableName]', i.indid,  5) or          c.name = index_col ('[YourTableName]', i.indid,  6) or          c.name = index_col ('[YourTableName]', i.indid,  7) or          c.name = index_col ('[YourTableName]', i.indid,  8) or          c.name = index_col ('[YourTableName]', i.indid,  9) or          c.name = index_col ('[YourTableName]', i.indid, 10) or          c.name = index_col ('[YourTableName]', i.indid, 11) or          c.name = index_col ('[YourTableName]', i.indid, 12) or          c.name = index_col ('[YourTableName]', i.indid, 13) or          c.name = index_col ('[YourTableName]', i.indid, 14) or          c.name = index_col ('[YourTableName]', i.indid, 15) or          c.name = index_col ('[YourTableName]', i.indid, 16)            )--外键：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