lists all the default constraints and the default values for the user tables in the database

SELECT  
        b.name AS TABLE_NAME,
        d.name AS COLUMN_NAME,
        a.name AS CONSTRAINT_NAME,
        c.text AS DEFAULT_VALUEFROM sys.sysobjects a INNER JOIN
        (SELECT name, id
         FROM sys.sysobjects 
         WHERE xtype = 'U') b on (a.parent_obj = b.id)
                      INNER JOIN sys.syscomments c ON (a.id = c.id)
                      INNER JOIN sys.syscolumns d ON (d.cdefault = a.id)                                          
 WHERE a.xtype = 'D'        
 ORDER BY b.name, a.name