Lets say you have a database made up of many tables. All of those tables have a field called "DateCreated" which cannot be null, but at the time the tables were created, you didn't think to set a default value for the field. Now you could open each table in design mode and set the default value manually, but here is an easier way:

The syntax to set a default value looks like this:

ALTER TABLE [table-name]
ADD CONSTRAINT constraint-name DEFAULT default-value FOR column-NAME;

While quicker that opening each table in design view this still only allows you to set the default value for a single table at a time. However, we can write some simple SQL that will generate a complete SQL Script for us:

SELECT 'ALTER TABLE [' + sysobjects.NAME +
'] ADD CONSTRAINT DF_' + sysobjects.NAME + '_DateCreated DEFAULT getdate()
FOR DateCreated;' from sysobjects inner join syscolumns on
sysobjects.id = syscolumns.id
inner join systypes on
syscolumns.xtype = systypes.xtype
WHERE syscolumns.NAME = 'DateCreated'

Run that query and each row returned will contain an Alter Table statement for each table. Simply highlight all the rows returned, then copy and paste them to a new query and run it. The SysObjects table contains one row for each object (table, column, constraint, default, log, rule, stored procedure, and so on) created within a database. This means you could write a query to update the sysobjects table directly and make this even easier, but if you mess up the sysobjects table your database could be trashed so use caution if you go that route.