Oracle入門——SQL查看CHECK約束信息
2024-08-29 13:50:13
供稿：網友
以下兩個語句均基于系統表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
