Two views of check constraint information in the query library (SQL SERVER)

server| View
The following two views are based on system table sysobjects, syscomments, and system view sysconstraints, including the table ID, table name, column ID, column name, check constraint ID, check constraint name, The check constraint status value and the contents of the check constraint, Tccview is Table-column-check view, the result is a column-level check constraint, Tcview is Table-check view, and the result is a table-level check constraint.

For the role of the value of the field status, see the article about the property settings for a check constraint without Enterprise Manager.

The following is a two-view code that can be used in any database in a SQL SERVER 2000 database.

Tccview:
SELECT top 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