>  기사  >  데이터 베이스  >  Oracle入门

Oracle入门

WBOY
WBOY원래의
2016-06-07 15:13:421315검색

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入 以下两个语句均基于系统表sysobjects、syscomments和系统视图sysconstraints,查询结果中包括表ID、表名、列ID、列名、CHECK约束ID、CHECK约束名、CHECK约束status值以及CHECK约束的内容,TCCView为

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入

  以下两个语句均基于系统表sysobjects、syscomments和系统视图sysconstraints,查询结果中包括表ID、表名、列ID、列名、CHECK约束ID、CHECK约束名、CHECK约束status值以及CHECK约束的内容,TCCView为Table-Column-Check View,结果中均为列级CHECK约束,TCView为Table-Check View,结果中均为表级CHECK约束。

       1.SELECT TOP 100 PERCENT a.id AS tableid, a.tablename, a.colid, a.columnname,
      a.datatype, a.length, b.constid AS checkid, b.checkname, b.status, b.content
FROM (SELECT sysobjects.name AS tablename, sysobjects.id,
              syscolumns.name AS columnname, syscolumns.colid,
              systypes.name AS datatype, syscolumns.length AS length
        FROM sysobjects, syscolumns, systypes
        WHERE sysobjects.xtype = 'u' AND sysobjects.id = syscolumns.id AND
              syscolumns.xtype = systypes.xtype AND
              systypes.xtype = systypes.xusertype AND sysobjects.status > 0)
      a LEFT OUTER JOIN
          (SELECT sysobjects.name checkname, sysobjects.status, sysconstraints.constid,
               sysconstraints.id, sysconstraints.colid, syscomments.text AS content
         FROM sysobjects, sysconstraints, syscomments
         WHERE xtype = 'c' AND sysobjects.id = sysconstraints.constid AND
               sysconstraints.constid = syscomments.id) b ON a.id = b.id AND
      a.colid = b.colid
ORDER BY a.tablename, a.columnname, b.checkname
   

     2.SELECT a.id AS tableid, a.tablename, b.constid AS checkid, b.checkname, b.status,
      b.content
FROM (SELECT sysobjects.id, sysobjects.name AS tablename
        FROM sysobjects
        WHERE sysobjects.xtype = 'u' AND sysobjects.status > 0) a LEFT OUTER JOIN
          (SELECT sysobjects.name checkname, sysobjects.status, sysconstraints.constid,
               sysconstraints.id, sysconstraints.colid, syscomments.text AS content
         FROM sysobjects, sysconstraints, syscomments
         WHERE xtype = 'c' AND sysobjects.id = sysconstraints.constid AND
               sysconstraints.constid = syscomments.id AND sysconstraints.colid = 0) b ON
      a.id = b.id

Oracle入门

성명:
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.