Microsoft – SQLServer – Column – Definitions – Default Constraints

Microsoft – SQLServer – Column – Definitions – Default Constraints

It is sometimes desirable to assign defaults to database columns.

It is easy enough to do:


  The sample below creates a table dbo.friendList.
  It automatically tracks the person who created a record 
  based on the addedBy column.
  And, the "date Added" via the dateAdded column.

create table [dbo].[friendList]

    [friendID] int bigint not null identity(1,1)

  , [active] bit not null  
        constraint defaultfriendListActive 
          default 1

  ,  [addedBy] sysname not null 
         constraint defaultfriendListAddedBy 
            default SYSTEM_USER

  ,  [dateAdded] datetime not null 
          constraint defaultfriendListDateAdded
             default getutcdate()


To list all column level default Constraints issue:

       db_name() as databaseName
     , schema_name(tblConstraintDefault.schema_id) as schemaName	
     , object_name(tblColumn.object_id) as objectName
     , as columnName
     , as defaultName					
     , tblConstraintDefault.is_system_named						
     , tblConstraintDefault.definition

FROM   sys.default_constraints tblConstraintDefault

    inner join sys.columns tblColumn

      on  tblConstraintDefault.parent_object_id = 

     and tblConstraintDefault.parent_column_id =

where  tblConstraintDefault.definition is not null

order by 

   , schema_name(tblConstraintDefault.schema_id)
   , object_name(tblColumn.object_id) 

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s