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:

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

FROM   sys.default_constraints tblConstraintDefault

    inner join sys.columns tblColumn

      on  tblConstraintDefault.parent_object_id = 
           tblColumn.object_id

     and tblConstraintDefault.parent_column_id =
          tblColumn.column_id

where  tblConstraintDefault.definition is not null

order by 

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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