熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle與SqlServer中獲取所有字段、主鍵、外鍵的sql語句

2013-11-13 22:15:16  來源: Oracle 

  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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.