查询库中的CHECK约束信息的两个视图（SQLSERVER）

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

关于字段status的值的作用，参见不用企业管理器的情况下得知CHECK约束的属性设置一文。

下面是两个视图的代码，可用于SQL SERVER 2000数据库中的任意数据库中。

TCCView：

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

TCView：

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

转载于:https://www.cnblogs.com/JoshuaDreaming/archive/2010/11/25/1888260.html