|
以下两个语句均基于系统表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
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 