SQL查看CHECK约束信息

王朝mssql·作者佚名  2006-11-24
窄屏简体版  字體: |||超大  

以下两个语句均基于系统表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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有  導航