查询库中的CHECK束缚信息的两个视图（SQL SERVER）
发布时间：2018-08-18 23:15:56 所属栏目：电商 来源：站长网
导读：以下两个视图均基于体系表sysobjects、syscomments和体系视图sysconstraints，查询功效中包罗表ID、表名、列ID、列名、CHECK束缚ID、CHECK束缚名、CHECK束缚status值以及CHECK束缚的内容，TCCView为Table-Column-Check View，功效中均为列级CHECK束缚，TCV
|
以下两个视图均基于体系表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 （编辑：河北网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
