SQL Server - Catalog Queries 2005/2008

List Table Check Constraints

This query will return a list of all the table check constraints for a SQL Server 2005/2008 database. This query can come in handy for research, or for creating code generators.

Query

SELECT check_constraints.name  as 'default_contraint_name' , 
       all_objects.name as 'tablename',
       all_columns.name as 'columnname',
       all_columns.column_id,     
       check_constraints.definition ,         
       check_constraints.object_id as 'constraint_id' , 
       all_columns.object_id as 'table_object_id' ,    
       check_constraints.type , 
       check_constraints.type_desc , 
       check_constraints.create_date , 
       check_constraints.modify_date          
FROM   SYS.check_constraints
join SYS.all_columns on 
check_constraints.parent_object_id =  all_columns.object_id
and check_constraints.parent_column_id = all_columns.column_id 
join SYS.all_objects on 
all_columns.object_id = all_objects.object_id 

Using this query

You can add further row restriction on the query if desired.

Default Contraint Table Schema Example

CREATE TABLE dbo.ClassWaitlist
( 
  classes_num_fk      INT             NOT NULL  , 
  student_id_fk       VARCHAR(15)     NOT NULL  
    CHECK ([ClassWaitList].[student_id_fk]>(0)) , 
  waitlist_type_code  VARCHAR(25)     NULL  DEFAULT ('z') , 
  waitlist_priority_code VARCHAR(10)  NULL  , 
  active_flg          TINYINT         NOT NULL  , 
  waitlist_comments   TEXT                NULL  , 
  CONSTRAINT PK_ClassWaitlist 
  PRIMARY KEY CLUSTERED (classes_num_fk ASC, student_id_fk ASC) 
  ON [PRIMARY] 
)

Default Contraint Syntax Example for Table Schema Above

 
ALTER TABLE [dbo].[ClassWaitlist]
ADD   CONSTRAINT CK_ClassWaitliststudent_id_fk
      CHECK ([ClassWaitList].[student_id_fk]>(0))

 Collapse All    Expand All
Custom Search

SQL Server Information and Resources