SQL Server - Catalog Queries 2005/2008

Server 2005 Catalog Query - List Table Default Constraints

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

Query

SELECT default_constraints.name  as 'default_contraint_name' , 
       all_objects.name as 'tablename',
       all_columns.name as 'columnname',
       all_columns.column_id,     
       default_constraints.definition ,         
       default_constraints.object_id as 'constraint_id' , 
       all_columns.object_id as 'table_object_id' ,    
       default_constraints.type , 
       default_constraints.type_desc , 
       default_constraints.create_date , 
       default_constraints.modify_date          
FROM   SYS.default_constraints
join SYS.all_columns on 
default_constraints.parent_object_id =  all_columns.object_id
and default_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 DF_ClassWaitlistwaitlist_type_code
DEFAULT 'z' FOR [waitlist_type_code]

 Collapse All    Expand All
Custom Search

SQL Server Information and Resources