我要投稿
  • 您当前的位置:57365.com -> 技术教程 -> 数据库教程 -> SQL server教程 -> 教程内容
  • [ 收藏本页教程 ]
  • 查询表主键外键信息的SQLSQL server]教程

    教程作者:佚名    教程来源:不详   教程栏目:SQL server教程    收藏本页
     

    我的BSOOC里需要一个查询表主键外键信息的SQL,昨晚研究到凌晨1点,终于能实现这个目标:
    Oracle:
    select o.obj# as objectId, o.name AS tableName, oc.name AS constraintName,
           decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
                  4, 'R', 5, 'V', 6, 'O', 7,'C', '?') as constraintType,
           col.name AS columnName
         
    from sys.con$ oc, sys.con$ rc,
         sys.obj$ ro,sys.obj$ o, sys.obj$ oi,
         sys.cdef$ c,
         sys.col$ col, sys.ccol$ cc, sys.attrcol$ ac
    where oc.con# = c.con#
      and c.obj# = o.obj#
      and c.rcon# = rc.con#(+)
      and c.enabled = oi.obj#(+)
      and c.robj# = ro.obj#(+)
      and c.type# != 8
      and c.type# != 12       /* don't include log groups */
      and c.con# = cc.con#
      and cc.obj# = col.obj#
      and cc.intcol# = col.intcol#
      and cc.obj# = o.obj#
      and col.obj# = ac.obj#(+)
      and col.intcol# = ac.intcol#(+)
      and o.name = 'your table'
     
    SQL Server:

    SELECT sysobjects.id objectId,
    OBJECT_NAME(sysobjects.parent_obj) tableName,
    sysobjects.name constraintName,
    sysobjects.xtype AS constraintType,
    syscolumns.name AS columnName
    FROM sysobjects INNER JOIN sysconstraints
    ON sysobjects.xtype in('C', 'F', 'PK', 'UQ', 'D')
     AND sysobjects.id = sysconstraints.constid
    LEFT OUTER JOIN syscolumns ON sysconstraints.id = syscolumns.id
    WHERE OBJECT_NAME(sysobjects.parent_obj)='your table'
     
    其它数据库还没时间去实现.

     
    我要投稿   -   广告合作   -   关于本站   -   友情连接   -   网站地图   -   联系我们   -   版权声明   -   设为首页   -   加入收藏   -   网站留言
    Copyright © 2009 - 20012 www.www.hxswjs.com All Rights Reserved.57365.com 版权所有