Return to Revisions

You can see the defaults and their definitions with this query:

select object_name(o.parent_object_id) as ObjectName
        , cl.name as ColumnName
        , o.name as DefaultName
        , c.text as DefaultDefintion
from sys.syscomments c
join sys.objects o on c.id = o.object_id
join sys.columns cl on cl.object_id = o.parent_object_id AND cl.default_object_id = o.object_id 
where o.type = 'D'

Edited to find user defined types:

    DECLARE @MyTable AS dbo.MyTableType; 

select object_name(o.parent_object_id) as ObjectName 
             , cl.name as ColumnName 
             , o.name as DefaultName 
             , c.text as DefaultDefintion
from tempdb.sys.syscomments c 
join tempdb.sys.objects o on c.id = o.object_id 
join tempdb.sys.columns cl on cl.object_id = o.parent_object_id AND cl.default_object_id = o.object_id 
where o.type = 'D'