Execute the following script to demonstrate the listing of all defaults for columns in AdventureWorks2008 database.

use AdventureWorks2008;

select

      TableName=object_name(parent_object_id),

      TableColumn = c.name,

      *

from sys.objects o

join sys.columns c

      on o.object_id = c.default_object_id

where o.type='D'

order by TableName, c.column_id

go